Detailed examples of how to operate multi-table associations in YII2

Recently, I encountered problems related to YII2 multi-table association at work. I found that there is not much information on this aspect on the Internet, so I thought about sorting it out myself so that I can refer to it when I need it in the future or friends who need it can refer to it. The following article mainly introduces you to the use of multi-table associations in YII2. Friends who need it can take a look below.

Preface

This article gives a brief introduction to the multi-table related query of YII2.0. The article introduces it in detail through the example code. I won’t say much below. Let’s take a look at the detailed introduction:

First, let’s explain the table structure

Table structure

Now there are order table, user table, product list table, product inventory table

##In YII, if you want to directly associate other tables for query, you need to define their association in the model first

Order

class Order extends \yii\db\ActiveRecord.{
 
 // 关联函数以get+要关联的数据表名来命名
 // 这是获取下订单的客户
 public function getUser(){
  
  // 第一个参数为要关联的子表模型类名，
  // 第二个参数指定 通过子表的user_id，关联主表的usesr_id字段
  // 这里写清楚点大概意思就是User.user_id => Order.user_id
 return $this->hasMany(User::className(), ['user_id' => 'user_id']);
 }
}

1, hasMany, hasOne use

There are two types of associations between tables in Yii2, they use to specify the association between two models.

●One-to-many: hasMany ●One-to-one: hasOne

●Return results: The return results of these two methods are both yiidbActiveQuery objects (if you want to finally return Standard array form, remember to add asArray() parameter)

●The first parameter: the class name of the associated model.

●The second parameter: is an array, where the keys are the attributes in the associated model and the values ​​are the attributes in the current model.

Associated use

Now let’s try to get an order

//获取订单信息
$order = Order::findOne(1);
//根据订单信息获取到用户信息
$user = $order->user;

Of course you can choose to use the with method, which looks simpler. The parameter of with is the name of the relationship, which is the user.

in getUser defined in the model.

//返回订单信息（包括用户信息）
$order = Order::find(1)->with('user');
//或者
$order = Order::find(1)->getUser();

The above code will generate and execute the following sql statement

SELECT * FROM order WHERE id=1;
SELECT * FROM user  WHERE user.user_id=order.user_id;

It can be seen from the above that there are two ways to access an association. Method

●If called as a function, an ActiveQuery object will be returned ($customer->getOrders()->all())

●If called as an attribute Method call will directly return the results of the model ($customer->orders)

Associated result cache

If the order table changes at this time, We hope that if you query again

$user = $order->user;

you will find that there is no change when you get the order again. The reason is that the database will only be queried when $order->user is executed for the first time, and the results will be cached, and sql will not be executed during subsequent queries.

So what if you want to execute sql again? You can execute

//先释放缓存
unset($order->user);
$order->user;

Cross-table query

The following is the key point! As you can see from the table structure diagram above, the User table and Order_goods are not directly related. So if we want to find out which products the user bought based on user information, we must associate the two tables through the Order table. So what to do? The first is the model layer. Because we are checking based on the user, we go to the User model layer to define the association.

User

##

public function getOrder() {
 return $this->hasMany(Order::className(), ['user_id' => 'user_id']);
}
 
public function getOrderGoods() {
 return $this->hasMany(OrderGoods::className(), ['order_id' => 'order_id'])->
  via('order');
}

Note here: The second one in getOrderGoods order_id refers to the order_id in the Order associated with getOrder, and the first order_id refers to the order_id in OrderGoods. but! We also have the simplest method, which is to use SQL statements!

$map = 'select
  user.name,
  order.id,
  order_goods.goods_id,
  goods.goods_name,
  stock.stock_count
  from user
  LEFT JOIN order   ON order.user_id = user.user_id
  LEFT JOIN order_goods ON order_goods.order_id = order.order_id
  LEFT JOIN goods   ON goods.goods_id = order_goods.goods_id
  LEFT JOIN stock   ON stock.goods_id = goods.goods_id';

$list1 = Article::findBySql($map)->asArray()->all();

This is basically the entire related part

Summary

The above is the detailed content of Detailed examples of how to operate multi-table associations in YII2. For more information, please follow other related articles on the PHP Chinese website!