An example of how multiple table associations operate in YII2

Recently encountered in the work of YII2 multi-table related issues, found online this aspect of the information is not much, so think of themselves to organize it, to facilitate their own in the future when needed or friends who need to refer to the study, the following this article mainly on the YII2 in the use of multi-table association, Need to have a friend below to see it together.

Objective

This paper makes a simple introduction to the Multi-table association query of YII2.0. The article through the example code introduced in very detailed, the following words do not say, come together to see the detailed introduction:

First, let's explain the table structure.

Table structure

Now there are order form, user table, commodity List table, Commodity inventory table

In Yii, if you want to directly correlate other tables for querying, you need to define their associations in the model first.

Order

Class Order extends \yii\db\activerecord. {//  association function to name the data table name to be associated with get+//This is the client public function that gets the order GetUser () {    ///The first parameter is the child table model class name to be associated,  //The second parameter specifies Through the user_id of the child table, the usesr_id field of the associated Main Table  //Here is a clear point about the meaning user.user_id = order.user_id return $this->hasmany (User: ClassName (), [' user_id ' = ' user_id ']); }}

1, Hasmany, hasone use

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

One-to-many: Hasmany one-to-one: HasOne

Results returned: The results of both methods are Yiidbactivequery objects (if you want to return a standard array form, remember to add the Asarray () parameter)

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 let's try to get an order

Get order Information $order = Order::findone (1);//obtain user information based on order information $user = $order->user;

Of course you can choose to use the With method, which looks concise, with the parameters of the relationship being the name of the relation and the user in the GetUser defined in the model.

Return order information (including user information) $order = Order::find (1)->with (' user ');//or $order = Order::find (1)->getuser ();

The code above generates and executes the following SQL statement

SELECT * from order WHERE id=1; SELECT * from user  WHERE user.user_id=order.user_id;

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)

Association result Cache

If the order table changes at this point, we want to query again.

$user = $order->user;

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 $order->user will go to the database query, and then the results will be cached, and later query will not execute SQL.

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

Release Cache unset ($order->user) first; $order->user;

Cross-table queries

Here's the point! Using the graph of the above table structure, we can see that the user table and the order_goods are not directly related, so if we want to find out what the users have bought from the user information, we will need to correlate the two tables with the order form. So what should we do? First or the model layer. Because we are based on the user to check, so to the user's model layer to define the association.

User

Public Function GetOrder () {return $this->hasmany (Order::classname (), [' user_id ' = ' user_id ']);} public function Getordergoods () {return $this->hasmany (Ordergoods::classname (), [' order_id ' = ' order_id ']),  via (' Order ');}

Note here:the second order_id in getordergoods refers to the order_id in the getorder associated order, and the first order_id refers to Ordergoods in order_id.

But! We also have the simplest method, that is, the use of SQL statements!

$map = ' Select  user.name,  order.id,  order_goods.goods_id,  goods.goods_name,  stock.stock_ Count from  user left  join order on   order.user_id = user.user_id left  join Order_goods on Order_ goods.order_id = order.order_id Left  join goods on   goods.goods_id = order_goods.goods_id Left  join stock On   stock.goods_id = goods.goods_id '; $list 1 = article::findbysql ($map)->asarray ()->all ();

This is basically the whole connection.

Summarize