Detailed explanation of the use of YII2 multi-table association

Recently, I encountered problems related to YII2 multi-table association in the project, and did not find suitable information, so I sorted 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. This article mainly introduces you to the use of multi-table associations in YII2. Friends who need it can take a look below. I hope to be helpful.

Preface

This article gives a brief introduction to the multi-table related query of YII2.0. The article introduces it in detail through 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, which are used 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 return the final 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 be generated and executed as follows sql statement

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

As you can see from the above, there are two ways to access an association

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

●If called as an attribute, the result of the model will be returned directly ($customer->orders)

Association result cache

If the order table changes at this time, we hope to query again

$user = $order->user;

When you get the order again, you will find that there is no change. The reason is that the database will be queried only 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 order_id in getOrderGoods refers to the Order associated with getOrder order_id, 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 association part

Related recommendations:

Detailed explanation of the simple extension of Yii framework batch insertion data Class

Detailed explanation of restful api authorization verification of yii2

Yii solves DeleteAll connection table deletion Error reporting

The above is the detailed content of Detailed explanation of the use of YII2 multi-table association. For more information, please follow other related articles on the PHP Chinese website!