The example explains how to use multi-Table Association in YII2, and the example describes yii2.

The example explains how to use multi-Table Association in YII2, and the example describes yii2.

Preface

This article briefly introduces the multi-table join query of YII2.0. The introduction of the Instance code in this article is very detailed. Let's take a look at the detailed introduction below:

First, describe the table structure.

Table Structure

Now there are order tables, user tables, product list tables, and product inventory tables.

In YII, If You Want To directly associate other tables for query, you must first define their associations in the model.

Order

Class Order extends \ yii \ db \ ActiveRecord. {// The Association function is named by get + The Name Of The data table to be associated. // This is the customer's public function getUser () {// The first parameter is the name of the subtable model class to be associated, // The second parameter specifies the user_id through the subtable, link the usesr_id field of the main table. // here, the point is clearly about User. user_id => Order. user_id return $ this-> hasMany (User: className (), ['user _ id' => 'user _ id']);}

1. Use 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-many: hasMany ● One-to-one: hasOne

● Returned results: the returned results of these two methods are all yiidbActiveQuery objects (if you want to return a standard array, add the asArray () parameter)

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

// Obtain the order information $ Order = order: findOne (1); // obtain the user information according to the order information $ user = $ Order-> user;

Of course, you can choose to use the with method, which looks concise. The with parameter is the name of the link, 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 above code will generate and execute the following SQL statement

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

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 the model is called as an attribute, the model result ($ customer-> orders) is returned directly)

Association result Cache

If the order table changes, we want to query it again.

$user = $order->user;

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 $ order-> user 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 you want to execute SQL again? Executable

// First release the cache unset ($ order-> user); $ order-> user;

Cross-Table query

Here's the focus! The figure in the table structure above shows that the User table and Order_goods table are not directly associated. If we want to find out which products the User has bought based on the User information, it is necessary to associate two tables through the Order table. So what should we do? The first is the model layer. Because we query data based on users, we define associations at the User's model layer.

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:The second order_id in getOrderGoods refers to order_id in the Order associated with getOrder, and the first order_id refers to order_id in OrderGoods.

But! The simplest way is to use 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';$list1 = Article::findBySql($map)->asArray()->all();

This is basically the entire association part.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message. Thank you for your support.