The use of join and Joinwith Multi-table association query in YII2

This paper mainly introduces the use of Multi-table Association query (join, Joinwith) in Yii2, and the friends can refer to it. We hope to help you.

Table structure

Now has the Customer table, the order form, the Book table, the author table,

Client table customer (ID customer_name)
Orders form order (ID order_name customer_id book_id)
Book table (ID book_name author_id)
Author table (ID author_name)

Model definition

The following is the definition of these 4 models, which only write out the associations

Customer

Class Customer extends \yii\db\activerecord{//this is the acquisition of the customer's order, by the above we know this is a one-to-many association, a customer has multiple orders public function getorders () {// The first parameter is the child table model class name to be associated,//The second parameter specifies the customer_id of the child table, the ID field of the associated primary table return $this->hasmany (Order::classname (), [' customer_id ' = > ' id ']);}}

Order

Class Order extends \yii\db\activerecord{//gets the order of the user public function GetCustomer () {//the same first parameter specifies the associated child table model class name//return $this- >hasone (Customer::classname (), [' id ' = ' customer_id ']); Gets the order all books Public Function getbooks () {//the same first parameter specifies the associated child table model class name//return $this->hasmany (Book::classname (), [' id ' = ' book_id ']);}}

Book

Class Book extends \yii\db\activerecord{//gets the author public Function Getauthor () {//the same first parameter specifies the associated child table model class name, return $this HasOne (Author::classname (), [' id ' = ' author_id ']);}}

Author

Class Autor extends \yii\db\activerecord{}

Hasmany, Hasone use

There are 2 associations between the tables in Yii2, which are used to specify the association between the two models.

Pair more: Hasmany

One to one: HasOne

Returns the result: both methods return the Yii\db\activequery object

First parameter: The class name of the associated model.

The second argument: is an array where the key is a property in the associated model, and the value is a property in the current model.

Use of associations

Now we get all the order information for a customer

Get a customer information $customer = Customer::findone (1); $orders = $customer->orders; All orders for this customer are obtained by The associated method (GetOrders ()) defined in 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 re-call

$orders = $customer->orders;

Once you get the order again you will find that there is no change. The reason is that only when the first execution of the $customer->orders will go to the database query, and then the results will be cached, and later query will not execute SQL.

So what if I want to execute SQL again? can perform

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

You can then fetch the data from the database.

Define multiple associations

Again, we can define multiple associations within the customer.
If you return an order with a total of more than 100.

Class Customer extends \yii\db\activerecord{public function getbigorders ($threshold = +) {return $this->hasmany ( Order::classname (), [' customer_id ' = ' + ' ID '])->where (' Subtotal >: Threshold ', [': Threshold ' + $threshold]) ->orderby (' id ');}}

Two ways to access the association

As above, if you use the

$customer->bigorders

will be given more than 100 of all orders. If you want to return an order greater than 200, you can write this

$orders = $customer->getbigorders (+)->all ();

From the above you can see that there are two ways to access an association

If called as a function, returns a Activequery object ($customer->getorders ()->all ())

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

With the use of see the following code, is to take a customer's order

Execute SQL statement: SELECT * FROM customer where Id=1$customer = Customer::findone (1);//Execute Sql:select * from order WHERE Customer_i D=1$orders1 = $customer->orders;//This does not execute SQL, directly using the above cached results $orders2 = $customer->orders;

If we want to remove 100 users now and then access each user's order, from the above we may write the following code

Execute SQL statement: SELECT * FROM customer LIMIT 100$customers = Customer::find ()->limit (+)->all (); foreach ($customers as $customer) {//execute Sql:select * from order WHERE customer_id= ... $orders = $customer->orders;//Process Order ... }

However, if this is true, the SQL will be executed in each of the foreach loops to query the data in the database. Because each $customer object is not the same.

In order to solve the above problem you can use Yii\db\activequery::with ().

Where the width parameter is the name of the relationship, and also the orders and customer in the Getorders,getcustomer defined in the model

First Execute Sql:select * from customer LIMIT 100;//SELECT * from Orders WHERE customer_id in (,...) $customers = Customer::find ()->limit (+)->with (' orders ')->all (); foreach ($customers as $customer) {// In this loop, no more sql is executed $orders = $customer->orders;//... handle $orders ...}

If you use Select to specify the returned column, make sure that the returned column contains the associated field for the associated model, otherwise the associated table's model will not be returned

$orders = Order::find ()->select ([' id ', ' Amount '])->with (' Customer ')->all ()//$orders [0]->customer The result will be null//because the specified associated field in the associated model (customer) is not returned in the previous select. If you add customer_id, $orders [0]->customer can return the correct result $orders = Order::find ()->select ([' id ', ' Amount ', ' customer_ ID '])->with (' Customer ')->all ();

Filter conditions for with Plus

Query for an order with a customer greater than 100

First Execute Sql:select * from customer where Id=1$customer = Customer::findone (1);//SQL statement to execute Query order: SELECT * from Order WHERE CU Stomer_id=1 and subtotal>100$orders = $customer->getorders ()->where (' subtotal>100 ')->all ();

100 customer inquiries, each customer's total of more than 100 of the order

The following code executes the SQL statement://select * from the customer LIMIT 100//SELECT * from the order WHERE customer_id in (,...) and subtotal>100$customers = Customer::find ()->limit ()->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, the activequery of orders, which is returned by this association, executes once $query->andwhere (' subtotal>100 ');

Using Joinwith for Table Association

We all know that you can use join on to write associations between multiple tables. Let's take a look at Joinwit's statement in Yii2.

Joinwith ($with, $eagerLoading = true, $joinType = ' left JOIN ')

The $with data type is a string or an array, and if it is a string, the name of the association defined in the model (which can be a child association).

If the array, the key is an association defined in the GETXXX format in model, the value is a further callback operation on this association.

$eagerLoading whether to load data for the associated model in $with.

$joinType join type, available values are: Left JOIN, INNER join, default value is left JOIN

The order table and the Customer table are associated in the same way as the left join. Find all orders and sort by Customer ID and order id $orders = Order::find ()->joinwith (' Customer ')->orderby (' Customer.id, Order.id ') All ();//The Order form and Customer table are associated with inner JOIN//Find all orders and books $orders = Order::find ()->innerjoinwith (' Books ')->all ();//Use Inner Join joins the Books Association and the Customer Association in the order. Callback filtering is also performed on the Custmer Association: Find out the order that the customer contains the book within 24 hours $orders = Order::find ()->innerjoinwith ([' Books ', ' Customer ' = function ($query) {$query->where (' Customer.created_at > '). (Time ()-24 * 3600));}]) ->all ();//Use the LEFT join to connect the books Association, Books Association, and then use the left Join Connection Author Association $orders = Order::find ()->joinwith (' Books.author ') ->all ();

In implementation, YII executes the SQL statement that satisfies the join query condition, populates the result into the main model, executes a query statement for each association, and populates the corresponding correlation model.

Order and books associate inner join, but do not get books associated data $orders = Order::find ()->innerjoinwith (' books ', false)->all ();

On condition

You can also specify an on condition when you define an association

Class User extends Activerecord{public function getbooks () {return $this->hasmany (Item::classname (), [' owner_id ' = > ' id '])->oncondition ([' category_id ' = + 1]);}}

Use in Joinwith

Query the main model (user) data first, SELECT user.* from the user left JOIN item on Item.owner_id=user.id and category_id=1//and then query the related model data according to the associated condition SE Lect * from Item WHERE owner_id in (...) 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, either with or directly with the property to access the association. This time the on condition is used as the Where condition.

SELECT * FROM user WHERE Id=10$user = user::findone (10);

Summarize

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

The associations defined in the model are then used in the with or Joinwith.

The callback method can also be specified when the association is used.

Then there is the ability to specify where or on conditions for the association, with, Joinwith.

Related recommendations:

Yii2 Realize QQ Interconnection Login

Yii2 Simple parsing using caching

YII2 realization of RBAC permission control