_php instance using join, joinwith Multiple Table association query in YII2

Table structure

Now have the Customer table, order form, Book table, 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

Here are the definitions of these 4 models, which only write the associations

Customer

Class Customer extends \yii\db\activerecord
{
//This is to get the customer's order from 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 from 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
{/
/Get 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 ']);
Get all books in order 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
{/
/get 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 that specify the associations between the two models.

One-to-many: Hasmany

One-to-one: Hasone

Return result: Both methods return results are yii\db\activequery objects

First parameter: The class name of the associated model.

Second parameter: is an array in which the key is an attribute 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 association method defined in the customer (GetOrders ()).

The above two lines of code generate the following SQL statement

SELECT * from Customer WHERE id=1;
SELECT * from order WHERE customer_id=1;

Associating result caching

If the customer's order has changed, we'll call again

$orders = $customer->orders;

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

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

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

Then you can take the data from the database.

Define multiple associations

Also, 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 = m)
{
return $this->hasmany (Order::classname (), [' customer_id ' => ' id '])
->where (' Subtotal >: Threshold ', [':] Threshold ' => $threshold])
->orderby (' id ');
}

Two ways of accessing the association

As above, if the use of

$customer->bigorders

Will get all orders that are greater than 100. If you want to return an order that is greater than 200, you can write

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

You can see from above that there are two ways to access an association

If invoked as a function, a Activequery object ($customer->getorders ()->all ()) is returned.

If invoked as a property, the result of the model is returned directly ($customer->orders)

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

Execute SQL statement: SELECT * from Customer WHERE id=1
$customer = customer::findone (1);
Executes Sql:select * from order WHERE customer_id=1
$orders 1 = $customer->orders;
This does not execute SQL, directly using the cache results above
$orders 2 = $customer->orders;

If we are going to take out 100 users and then visit 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 ...
}

However, if you really want to write this, SQL will be executed once in each loop of foreach to query the data in the database. Because each $customer object is not the same.

To solve the above problem, you can use Yii\db\activequery::with ().

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

Execute Sql:select * from Customer LIMIT first;
SELECT * From to Orders WHERE customer_id in (1,2,...)
$customers = Customer::find ()->limit
->with (' orders ')->all ();
foreach ($customers as $customer) {
//No more SQL is executed during this loop
$orders = $customer->orders;
... handle $orders ...
}

If you use a select to specify the returned column, be sure to ensure that the returned column contains the associated field of the associated model, otherwise it will not return the form of the associated table

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

To add the filter conditions

Query for an order with a customer greater than 100

First executes Sql:select * from Customer WHERE id=1
$customer = customer::findone (1);
To execute the SQL statement that queries the order again: SELECT * from Order WHERE customer_id=1 and subtotal>100
$orders = $customer->getorders ()- >where (' subtotal>100 ')->all ();

For 100 customers, orders with a sum greater than 100 per customer

The following code executes the SQL statement: 
//select * FROM Customer LIMIT
//select * To order WHERE customer_id in (1,2,...) and subtotal>100
$customers = Customer::find ()->limit ()->with ([
' orders ' => function ($query) {
$query->andwhere (' subtotal>100 ');
},
])->all ();

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

That is to say, to the Activequery returned by the Association of Orders, to perform a $query->andwhere (' subtotal>100 ') again;

Using Joinwith to make table associations

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 ')

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

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

$eagerLoading whether to load data for the model associated with $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 with a left join.
//Find all orders, sorted by Customer ID and order id
$orders = order::find ()->joinwith (' Customer ')->orderby (' Customer.id, Order.id ')->all ();
The Order form and Customer table are inner join to associate
//Find all orders and books
$orders = Order::find ()->innerjoinwith ()->all ();
Use the Books Association and the Customer association in the inner join connection order.
//and callback Filtering for Custmer Association: Find out the order in which the customer contains the book within 24 hours
$orders = Order::find ()->innerjoinwith (
' books ',
' Customer ' => function ($query) {
$query->where (' Customer.created_at > '). (Time ()-3600))
->all ();
Use left JOIN to connect Books Association, books Association to author association with left JOIN connection
$orders = Order::find ()->joinwith (' Books.author ')-> All ();

In implementation, YII executes the SQL statement that satisfies the join query criteria, populates the main model with the results, and then executes a query statement for each association and populates the corresponding association model.

Order and books are associated with inner join, but do not get the data associated with the books association
$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]);
}

Using in Joinwith

Query the main model (USER) data first, SELECT user.* from User left JOIN item on item.owner_id=user.id and category_id=1
//And then query related based on association criteria Model Data 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 ();

If you are not using a JOIN operation, you are using with or accessing the association directly with the attribute. At 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 a good association in the model (such as orders in GetOrders for an association)

The association defined in the model is then used in the with or Joinwith.

The callback method can also be specified when using the association.

Again, you can specify where or on conditions for associations, with, Joinwith

This part is actually very many, also a bit disorderly, some function did not say finish, such as three Table association, inverse Association.

The most basic operations are basically these. If there is any place you want to know, you can exchange replies.

The above is a small set of Yii2 to introduce a number of related inquiries (join, Joinwith) of the relevant knowledge, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!