Implementation of Yii2 ActiveRecord multi-table association and multi-table association search

Yii’s ActiveRecord is a class that deals with databases, which is the M (model layer) in MVC and the O (Object) of ORM.

A common question. Recently, through the feedback in the group, I feel that many people still do not understand this issue. Today I have explained this problem clearly. Let’s take a look at the multi-table association of yii2 ActiveRecord and how to optimize this association.

Scenario requirements:

Suppose we have a user table user and a user channel table auth. The two data tables are related one-to-one through user.id and auth.uid. Now it is necessary to display the source channel source of the auth table in the user list, and the channel must be searchable.

First we generate user and auth series related models and operations through gii. No detailed explanation is given here. For gii operations, please refer to xxx

I think we will continue to look at the important steps:

1. Find the AR model class commonmodelsUser.php corresponding to the user table, and associate it in this class file. After the auth table

/**
* 关联auth表
*/
public function getAuth()
{
// hasOne要求返回两个参数 第一个参数是关联表的类名 第二个参数是两张表的关联关系
// 这里uid是auth表关联id, 关联user表的uid id是当前模型的主键id
return $this->hasOne(common\models\Auth::className(), ['uid' => 'id']);
}

is set up, it does not mean that the two data tables are automatically associated! We visit the user list page (the list page is generated by GII, we have not operated it so far). By viewing the Database Queries through debug, it is not difficult to find that the actual query is not associated with the auth table

2. Add the association table in the gridview Source channel field source

 [
// other columns
'auth.source',
]
]); ?>

Some students feel doubtful. Didn’t it say above that there is no association? How can I use auth.source directly?

Don’t worry, now we open debug to see the actual query.

We will find that there are many operations like select * from `auth` where uid = xxx;. If your paging defaults to 20 pieces of data, there will be 20 similar queries.

Let’s first figure out what happened?

Actually, this is the basic knowledge of php. When reading or writing a non-existent member variable of an object, the __get() __set() magic functions are automatically called. Yii also takes advantage of this to implement it!

This operation is almost the same as most people encapsulating the method in gridview to obtain related table data, but! 20 SQL queries obviously increase a lot of overhead. It would be great if this was a left join operation!

3. Optimize sql

What we need to optimize is:

20 sql changes into 1 sql

Only get the fields required by the related table

Some students are asking, here is the operation that comes with Yii, how to optimize it ? We return to the acquisition of data sources and find that the data of the user list is provided through the search method of the userSearch model.

That is to say, our data query does not actually perform related table query! In this case, we will add related query to UserSearch

$query = User::find();
$query->joinWith(['auth']);
$query->select("user.*, auth.source");

Let’s refresh the user list page again, and then through debug analysis, we find that two SQL statements have attracted our attention

SELECT `user`.*, `auth`.`source` FROM `user` LEFT JOIN `auth` ON `user`.`id` = `auth`.`uid` LIMIT 20
SELECT * FROM `auth` WHERE `user_id` IN (20个uid);

That is to say We have achieved the goal of optimizing SQL. Through debug analysis, we found that the query time of DB has been reduced a lot.

4. Add queries to related table fields

The search model in gridview is also implemented through searchModel. This model controls which fields are searchable and which fields are not searchable through rules.

We now need to make the source of the association table searchable, so we define an attribute source in the searchModel and add it to the rules

public $source;
public function rules()
{
return [
// other rules
['source', 'safe'],
];
}

Then we modify the auth.source in the gridview

// 'auth.source',
[
'attribute' => 'source',
'value' => 'auth.source',
'label' => '渠道来源',
],

Up to this point, our interface is ok. There is still one step left to implement programmatic search. We can just add the new source condition where the data source is obtained

$query->andFilterWhere([
// other params
'auth.source' => $this->source,
]);

The following is a supplement to yii. Some uses of ActiveRecord

1, object to array

$model = new ActiveRecord();
$model.toArray();

Since ActiveRecord is not a simple array, it cannot be json_encoded directly, otherwise the information will be incomplete.

Solution: $model.toArray(); This turns it into a simple array and can be json_encoded.

2. Get the ActiveRecord id directly through the name or other fields.

$nIdcId = idc_info::model()->find('name like :name',array(':name'=>"%".$strIdcName."%"))->id;

The method I often used in the past is (now I find it very vulgar):

$idc = Idc::model()->find("...");
$id = $idc->id;

3, understanding of the model

$accModel = call_user_func(array(ActiveRecordName, 'model'));
$model  = $accModel->findByPk($id);

The above is what the editor introduces to you Yii2 ActiveRecord multi-table association and implementation of multi-table association search related knowledge, I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank you all for your support of the PHP Chinese website!

For more articles related to the implementation of Yii2 ActiveRecord multi-table association and multi-table association search, please pay attention to the PHP Chinese website!