Use join and joinwith multi-table join queries in Yii2

This article mainly introduces data related to join and joinwith multi-table join queries in Yii2. it is very good and has reference value. if you need it, you can refer to the table structure below.

Currently, there are customer tables, order tables, book tables, and author tables,

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 the four models. only the associations are written.

Customer

Class Customer extends \ yii \ db \ ActiveRecord {// This is to obtain the Customer's order. as we know above, this is a one-to-many association. a Customer has multiple orders: public function getOrders () {// The first parameter is the name of the subtable model class to be associated, // The second parameter specifies the customer_id through the subtable, return $ this-> hasvalues (Order: className (), ['customer _ id' => 'id']);}

Order

Class Order extends \ yii \ db \ ActiveRecord {// Obtain the public function getCustomer () of the Order User () {// specify the associated sub-table model class name as the first parameter // return $ this-> hasOne (Customer: className (), ['id' => 'customer _ id']);} // Obtain all books in the order public function getBooks () {// specify the associated sub-table model class name as the first parameter // return $ this-> hasexample (Book: className (), ['id' => 'Book _ id']);}

Book

Class Book extends \ yii \ db \ ActiveRecord {// Obtain the author's public function getAuthor () {// similarly, the first parameter specifies the name of the associated sub-table model class return $ this-> hasOne (Author: className (), ['id' => 'author _ id']);}

Author

class Autor extends \yii\db\ActiveRecord{}

HasOne and hasOne

There are two types of associations between tables in yii2. they are used to specify the associations between two models.

One-to-multiple: haswon

One-to-one: hasOne

Returned results: the returned results of both methods are yii \ db \ ActiveQuery objects.

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

The second parameter is an array. 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.

// Obtain a customer information $ Customer = customer: findOne (1); $ orders = $ customer-> orders; // getOrders () is used to obtain all orders of the Customer.

The above two lines of code 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 can call it again.

$orders = $customer->orders;

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 $ customer-> orders is executed for the first time, and the results will be cached. the SQL statement will not be executed in future queries.

So what if I want to execute SQL again? Executable

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

Then you can retrieve data from the database.

Define multiple associations

Similarly, we can also define multiple associations in the Customer.
For example, an order with a total number greater than 100 is returned.

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

If you use

$customer->bigOrders

You will get more than 100 of all orders. If you want to return an order greater than 200, you can write it like this.

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

We can see from the above that there are two ways to access an association.

If you call a function, an ActiveQuery object ($ customer-> getOrders ()-> all () is returned ())

If you call an attribute, the model result ($ customer-> orders) is directly returned)

See the following code for the use of:

// Execute the SQL statement: SELECT * FROM customer WHERE id = 1 $ customer = Customer: findOne (1); // execute the SQL statement: SELECT * FROM order WHERE customer_id = 1 $ orders1 = $ customer-> orders; // The preceding cache result $ orders2 = $ customer-> orders will not be executed;

If we need to retrieve 100 users and then access the orders of each user, we may write the following code from the above:

// Execute the SQL statement: SELECT * FROM customer LIMIT 100 $ MERS = Customer: find ()-> limit (100)-> all (); foreach ($ customers as $ customer) {// execute SQL: SELECT * FROM order WHERE customer_id =... $ orders = $ customer-> orders; // process the order ...}

However, if this is the case, an SQL statement is executed in each loop of foreach to query data in the database. Because every $ customer object is different.

To solve the problem above, you can use yii \ db \ ActiveQuery: ().

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

// Execute SQL first: SELECT * FROM customer LIMIT 100; // SELECT * FROM orders WHERE customer_id IN ,...) $ MERS = Customer: find ()-> limit (100)-> with ('orders ')-> all (); foreach ($ customers as $ customer) {// The SQL statement will no longer be executed in this loop. $ orders = $ customer-> orders ;//... handle $ orders ...}

If select is used to specify the returned columns, make sure that the returned columns contain the correlated fields of the associated Model. Otherwise, the Model of the associated table is not returned.

$ Orders = Order: find ()-> select (['id', 'amount '])-> with ('customer')-> all (); // $ orders [0]-> the customer result will be null // because the preceding select statement does not return the specified Association field in the associated model (customer. // If customer_id is added, $ orders [0]-> customer can return the correct result $ orders = Order: find ()-> select (['id ', 'amount ', 'Customer _ id'])-> with ('customer')-> all ();

Apply filter conditions to

Query orders with a customer greater than 100

// First run SQL: SELECT * FROM customer WHERE id = 1 $ customer = Customer: findOne (1); // then run the SQL statement to query the order: SELECT * FROM order WHERE customer_id = 1 AND subtotal> 100 $ orders = $ customer-> getOrders ()-> where ('subtotal> 100')-> all ();

Query orders of more than 100 customers.

// The following code executes the SQL statement: // SELECT * FROM customer LIMIT 100 // SELECT * FROM order WHERE customer_id IN ,...) AND subtotal> 100 $ MERS = 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, execute $ query-> andWhere ('subtotal> 100') for the ActiveQuery returned by the orders Association ');

Use joinWith for table Association

We all know that join on can be used to write associations between multiple tables. Let's take a look at the joinWit statement in yii2.

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

$ With is a string or array. if it is a string, it is the name of the Association defined in the model (which can be a subassociation ).

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

$ EagerLoading: whether to load the data of the model associated with $.

$ JoinType: JOIN type. available values: left join and inner join. the default value is left join.

// The order table and customer table are joined in Left join mode. // Search for all orders and sort them by customer ID and Order ID $ orders = Order: find ()-> joinWith ('customer')-> orderBy ('customer. id, order. ID')-> all (); // The Order table and the customer table are joined in the form of Inner join // query all orders and books $ orders = Order: find () -> innerJoinWith ('books ')-> all (); // use inner join to join the books Association in the order and the customer Association. // Perform callback filtering on the custmer Association again: find the Order $ orders = Order: find ()-> innerJoinWith (['books ', 'customer' => function ($ query) {$ query-> where ('customer. created_at> '. (time ()-24*3600);}])-> all (); // use left join to join books, books Association and then use left join to connect author Association $ orders = Order: find ()-> joinWith ('books. author ')-> all ();

In terms of implementation, Yii first executes the SQL statement that meets the JOIN query conditions, fills the result in the main model, then executes a query statement for each JOIN, and fills in the corresponding association model.

// Order and books are associated with inner join, but the corresponding data of books Association is not obtained. $ 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]);}}

Use in joinWith

// First query the data of the master model (User), SELECT user. * FROM user left join item ON item. owner_id = user. id AND category_id = 1 // then SELECT * FROM item WHERE owner_id IN (...) based on the association conditions (...) AND category_id = 1 // both use the on condition during the query. $ Users = User: find ()-> joinWith ('books ')-> all ();

If the join operation is not used, with is used or the association is accessed directly using attributes. In this case, the on condition is used as the where condition.

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

Summary

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

Then, use the associations defined in the model in with or joinWith.

You can also specify the callback method when using Association.

Then, you can specify the where or on condition for the association, with, and joinWith.

In fact, this part is quite a bit messy, and some features have not been completed, such as three table associations and inverse associations.

These are the most basic operations. If you want to know more, you can reply.

The above section describes the join and joinwith knowledge about multi-table join queries in Yii2. I hope to help you. if you have any questions, please leave a message, the editor will reply to you in a timely manner. I would like to thank you for your support for PHP chinnet!

For more articles on using join and joinwith multi-table join queries in Yii2, please follow PHP Chinese network!