YII2 Multiple Table Association query (join, Joinwith) with is not executing SQL

Multiple Table association queries in YII2 (Join, Joinwith) We use examples to illustrate that this part of the table structure now has the Customer table, the order table, the Book table, the author table, and the Customer table (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 below Is the definition of these 4 models, just write the associated customerclassCustomerextends\yii\db\activerecord{//This is to get the customer's order, by the above we know this is a one-to-many association, a customer has multiple orders     Public functiongetorders () {//The first parameter is the child table model class name to be associated,//The second parameter specifies the ID field of the associated primary table through the customer_id of the child table        return $this->hasmany (Order::classname (), [' customer_id ' = ' id ']); }} copy Code orderclassOrderextends\yii\db\activerecord{//get the user the order belongs to     Public functionGetCustomer () {//the same first parameter specifies the associated child table model class name//        return $this->hasone (Customer::classname (), [' id ' = ' customer_id ']); }    //get all the books in the order     Public functionGetbooks () {//the same first parameter specifies the associated child table model class name//        return $this->hasmany (Book::classname (), [' id ' = ' book_id ']); }} copy Code BookclassBookextends\yii\db\activerecord{//get the author of a book     Public functionGetauthor () {//the same first parameter specifies the associated child table model class name        return $this->hasone (Author::classname (), [' id ' = ' author_id ']); }} copy code authorclassAutorextends\yii\db\activerecord{} There are 2 associations between copy code Hasmany, hasone using tables in Yii2, which are used to specify associations between two models. One-to-many: Hasmany one-to-one: HasOne returns results: Both methods return the result as the first parameter of the Yii\db\activequery object: 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. Associated use 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 two lines of code above the copy code generate the following SQL statement select* FROM Customer WHERE id=1; SELECT* FROM Order WHERE customer_id=1Copy Code Association result cache if the customer's order changes, we re-call$orders=$customer-Orders ; Copy the code again to get the order when you will find no change. The reason is that it will only be executed the first time$customer-orders will go to the database inside the 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 performunset($customer-orders);$customer-orders; Copy the code and then you can fetch the data from the database. Define multiple associations Similarly, we can also define multiple associations within the customer. If you return an order with a total of more than 100. classCustomerextends\yii\db\activerecord{ Public functionGetbigorders ($threshold= 100)    {        return $this->hasmany (Order::classname (), [' customer_id ' = ' id '])            ->where (' Subtotal >: Threshold ', [': threshold ' + =$threshold])            ->orderby (' id '); The two access methods associated with the copy code are as above, if you use the$customer-bigorders Copy code will get all orders greater than 100. If you want to return an order greater than 200, you can write this$orders=$customer->getbigorders (a)All (); Copy code from above you can see that there are two ways to access an association if called as a function, a Activequery object is returned ($customer->getorders ()All ()) if called as an attribute, returns the result of the model directly ($customer-Orders ) with the use of 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_id=1$orders 1=$customer-orders;//this does not execute SQL, directly using the above cached results$orders 2=$customer-orders; Copy the code if we are going to take out 100 users and then access each user's order, from the above we may write the following code//Execute SQL statement: SELECT * from Customer LIMIT$customers= Customer::find ()->limit (+)All ();foreach($customers  as $customer) {    //Execute Sql:select * from order WHERE customer_id= ...    $orders=$customer-orders; //Process Order ... Copy Code However, if this is true, the SQL will be executed once in each of the foreach loops to query the data in the database. Because each$customer objects are not the same. In order to solve the above problem can use Yii\db\activequery::With (). Where the width parameter is the name of the relationship and is defined within the model getorders,orders and customer in the GetCustomer//First Execute Sql:select * from customer LIMIT 100;//SELECT * from Orders WHERE customer_id in (,...)$customers= Customer::find ()->limit (100)    ->with (' orders ')All ();foreach($customers  as $customer) {    //no more SQL will be executed at this time of the cycle.    $orders=$customer-orders; //... handle $orders ...Copy Code 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 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 (); Copy the code to the with filter to query a customer greater than 100 of the order//First Execute Sql:select * from customer WHERE id=1$customer= Customer::findone (1);//The SQL statement that executes the query order: SELECT * from Order WHERE customer_id=1 and subtotal>100$orders=$customer->getorders ()->where (' subtotal>100 ')All (); Copy Code query 100 customer, each customer's total is greater 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 (); Copy code here the width parameter is an array, the key is the associated name, and the value is the callback function. That is to say, the Activequery returned for the orders this association is executed once$query->andwhere (' subtotal>100 '); Use Joinwith for Table Association We all know that you 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 ' )$withThe 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. $eagerLoadingis loaded indata for the associated model in the $with. $joinTypeJoin type, available values are: LeftJOIN, INNERJOIN, the default value is leftJOIN //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 ();//order form and Customer table are associated with inner JOIN//Find all orders and books$orders= Order::find ()->innerjoinwith (' books ')All (); Copy Code//use the inner join to connect the books Association and the Customer Association in order. And then callback filtering for the Custmer Association: Find out the order that the customer contains books 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 the implementation of replication code, 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 (); Copy code on condition You can also specify an on condition when you define an associationclassUserextendsactiverecord{ Public functionGetbooks () {return $this->hasmany (Item::classname (), [' owner_id ' + ' = ' id '])->oncondition ([' category_id ' = 1]); The copy code is used in Joinwith//Query the main model (user) data first, SELECT user.* from the user left JOIN the item on Item.owner_id=user.id and category_id=1//and then query the related model data according to the association condition SELECT * from Item WHERE owner_id in (...) and category_id=1//both use the on condition during the query. $users= User::find ()->joinwith (' books ')all (); Copy code 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 (10Copy Code Summary first you need to define the association in the model (for example, orders in GetOrders as an association) and then use the associations defined in the model with or Joinwith. The callback method can also be specified when the association is used. Then there is the association, with, Joinwith specify where or on conditions this part is actually very much, also a bit chaotic, some functions did not say finish, such as three Table Association, inverse Association and so on. This is basically the most basic operation. If there is any place you want to know, you can reply to the exchange. AA

View Code

Http://www.cnblogs.com/yiifans/p/3786374.html

YII2 Multiple Table Association query (join, Joinwith) with is not executing SQL