Use join and joinwith multi-table association queries in Yii2

Table structure

Now there are customer table, order table, book table, author table,

Customer table Customer (id customer_name)
Order table Order (id order_name customer_id book_id)
Book table (id book_name author_id)
Author table (id author_name)

Model definition

The following are the definitions of these four models, only the relationships among them are written

Customer

class Customer extends \yii\db\ActiveRecord
{
// 这是获取客户的订单，由上面我们知道这个是一对多的关联，一个客户有多个订单
public function getOrders()
{
// 第一个参数为要关联的子表模型类名，
// 第二个参数指定 通过子表的customer_id，关联主表的id字段
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
}

Order

class Order extends \yii\db\ActiveRecord
{
// 获取订单所属用户
public function getCustomer()
{
//同样第一个参数指定关联的子表模型类名
//
return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
}
// 获取订单中所有图书
public function getBooks()
{
//同样第一个参数指定关联的子表模型类名
//
return $this->hasMany(Book::className(), ['id' => 'book_id']);
}
}

Book

class Book extends \yii\db\ActiveRecord
{
// 获取图书的作者
public function getAuthor()
{
//同样第一个参数指定关联的子表模型类名
return $this->hasOne(Author::className(), ['id' => 'author_id']);
}
}

Author

class Autor extends \yii\db\ActiveRecord
{
}

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 yii\db\ ActiveQuery object

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

The second parameter: is an array, where the key is the attribute in the associated model and the value is the attribute in the current model.

Associated use

Now we get all the order information of a customer

// 获取一个客户信息
$customer = Customer::findOne(1);
$orders = $customer->orders; // 通过在Customer中定义的关联方法（getOrders()）来获取这个客户的所有的订单。

The above two lines of code will generate the following sql statement

SELECT * FROM customer WHERE id=1;
SELECT * FROM order WHERE customer_id=1;

Association result cache

If the customer's order changes, we will call it again

$orders = $customer->orders;

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

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

unset($customer->orders);
$customer->orders;

and then you can fetch data from the database.

Define multiple associations

Similarly, we can also define multiple associations in Customer.
If the total number of orders is returned greater than 100.

class Customer extends \yii\db\ActiveRecord
{
public function getBigOrders($threshold = 100)
{
return $this->hasMany(Order::className(), ['customer_id' => 'id'])
->where('subtotal > :threshold', [':threshold' => $threshold])
->orderBy('id');
}
}

Two associated access methods

As above, if you use

$customer->bigOrders

All orders greater than 100 will be obtained. If you want to return orders greater than 200, you can write like this

$orders = $customer->getBigOrders(200)->all();

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

If you use the function When called in the method, an ActiveQuery object ($customer->getOrders()->all()) will be returned.

If called in the attribute method, the result of the model will be returned directly ($customer- >orders)

Look at the following code for using with, which is to take a customer’s order

// 执行sql语句: SELECT * FROM customer WHERE id=1
$customer = Customer::findOne(1);
//执行sql：SELECT * FROM order WHERE customer_id=1
$orders1 = $customer->orders;
//这个不会执行sql，直接使用上面的缓存结果
$orders2 = $customer->orders;

If we want to take out 100 users now, and then access each user From the above understanding, we may write the following code

// 执行sql语句: SELECT * FROM customer LIMIT 100
$customers = Customer::find()->limit(100)->all();
foreach ($customers as $customer) {
// 执行sql: SELECT * FROM order WHERE customer_id=...
$orders = $customer->orders;
// 处理订单。。。
}

However, if we really want to write it like this, sql will be executed once in each loop of foreach to go to the database. Query data inside. Because each $customer object is different.

In order to solve the above problem, you can use yii\db\ActiveQuery::with().

The width parameter is the name of the relationship, that is, getOrders defined in the model, orders and customer in getCustomer

// 先执行sql: SELECT * FROM customer LIMIT 100;
// SELECT * FROM orders WHERE customer_id IN (1,2,...)
$customers = Customer::find()->limit(100)
->with('orders')->all();
foreach ($customers as $customer) {
// 在这个循环的时候就不会再执行sql了
$orders = $customer->orders;
// ...handle $orders...
}

If select is used to specify the return Column, be sure to ensure that the returned column contains the associated fields of the associated model, otherwise the Model of the associated table will not be returned

$orders = Order::find()->select(['id', 'amount'])->with('customer')->all();
// $orders[0]->customer 的结果将会是null
// 因为上面的select中没有返回所关联的模型（customer）中的指定的关联字段。
// 如果加上customer_id，$orders[0]->customer就可以返回正确的结果
$orders = Order::find()->select(['id', 'amount', 'customer_id'])->with('customer')->all();

Add filter conditions to with

Query the orders of a customer greater than 100

//首先执行sql： SELECT * FROM customer WHERE id=1
$customer = Customer::findOne(1);
// 再执行查询订单的sql语句：SELECT * FROM order WHERE customer_id=1 AND subtotal>100
$orders = $customer->getOrders()->where('subtotal>100')->all();

Query the orders of 100 customers, the total of each customer is greater than 100

// 下面的代码会执行sql语句:
// SELECT * FROM customer LIMIT 100
// SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100
$customers = Customer::find()->limit(100)->with([
'orders' => function($query) {
$query->andWhere('subtotal>100');
},
])->all();

Here the width parameter is an array, the key is the associated name, and the value is the callback function.

That is to say, for the ActiveQuery returned by the orders association, execute $query->andWhere('subtotal>100');

Use joinWith for table association

We all know that we can use join on to write associations between multiple tables. First look at the declaration of joinWit in yii2

joinWith( $with, $eagerLoading = true, $joinType = 'LEFT JOIN' )

$with The data type is a string or an array. If it is a string, it is the name of the association defined in the model (can be a sub-association ).

If it is an array, the key is the association defined in the getXXX format in the model, and the value is the further callback operation for this association.

$eagerLoading Whether to load the data of the model associated in $with.

$joinType connection type, available values ​​are: LEFT JOIN, INNER JOIN, the default value is LEFT JOIN

// 订单表和客户表以Left join的方式关联。
// 查找所有订单,并以客户 ID 和订单 ID 排序
$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();
// 订单表和客户表以Inner join的方式关联
// 查找所有的订单和书
$orders = Order::find()->innerJoinWith('books')->all();
// 使用inner join 连接order中的 books关联和customer关联。
// 并对custmer关联再次进行回调过滤：找出24小时内注册客户包含书籍的订单
$orders = Order::find()->innerJoinWith([
'books',
'customer' => function ($query) {
$query->where('customer.created_at > ' . (time() - 24 * 3600));
}
])->all();
// 使用left join连接 books关联，books关联再用left join 连接 author关联
$orders = Order::find()->joinWith('books.author')->all();

In implementation, Yii first executes JOIN query conditions that satisfy SQL statement, fill the results into the main model, then execute a query statement for each association, and fill in the corresponding association model.

// Order和books关联 inner join ,但不获取books关联对应的数据
$orders = Order::find()->innerJoinWith('books', false)->all();

On condition

You can also specify the on condition when defining the association

class User extends ActiveRecord
{
public function getBooks()
{
return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
}
}

Used in joinWith

//先查询主模型（User）的数据， SELECT user.* FROM user LEFT JOIN item ON item.owner_id=user.id AND category_id=1
// 然后再根据关联条件查询相关模型数据SELECT * FROM item WHERE owner_id IN (...) AND category_id=1
// 这两个在查询的过程中都使用了 on条件。
$users = User::find()->joinWith('books')->all();

If the join operation is not used, even if you use with or directly access the association with attributes. At this time, the on condition will be used as the where condition.

// SELECT * FROM user WHERE id=10
$user = User::findOne(10);

Summary

First you need to define the association in the model (for example, Orders in getOrders is an association)

Then in with or joinWith Use the association defined in the model.

You can also specify a callback method when using association.

Furthermore, you can specify where or on conditions for association, with, and joinWith

This part is actually very many and a bit confusing. Some functions have not been explained, such as three table associations, Inverse correlation, etc.

The most basic operations are roughly these. If there is anything else you would like to know more about, please reply to the post.

The above is the relevant knowledge of multi-table related queries (join, joinwith) in Yii2 introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply in time. Ours. I would also like to thank you all for your support of the PHP Chinese website!

For more articles related to using join and joinwith multi-table association queries in Yii2, please pay attention to the PHP Chinese website!