Implementation of Yii2 ActiveRecord multi-Table Association and multi-Table Association search, yii2activerecord

Implementation of Yii2 ActiveRecord multi-Table Association and multi-Table Association search, yii2activerecord

ActiveRecord of Yii is a class for dealing with databases, that is, M (model layer) in MVC and O (Object) of ORM ).

An old-fashioned question. According to feedback from the group recently, many people still do not understand the problem. I have understood this question today. Let's see how yii2 ActiveRecord is associated with multiple tables and how to optimize this association.

Scenario requirements:

Assume that we have a user table and a user channel table auth. The two data tables are associated One to One using user. id and auth. uid. The source channel of the auth table needs to be displayed in the user list, and this channel can be searched.

First, we first generate models and operations related to the user and auth series through gii. For details about gii operations, refer to xxx.

I think the following steps are important:

1. Find the AR model class common \ models \ user. php corresponding to the User table and associate the auth table in this class file.

/*** Associate the auth table */public function getAuth () {// hasOne requires that two parameters be returned. The first parameter is the class name of the associated table, and the second parameter is the association relationship between the two tables. // uid is the id of the associated auth table, the uid id associated with the user table is the primary key idreturn $ this-> hasOne (common \ models \ Auth: className (), ['uid' => 'id']);}

After setting, it does not mean that two data tables are automatically associated! We access the user list page (This list page is generated using gii, but we have not performed any operations currently). It is not difficult to view the Database Queries through debug, and the actual query is not associated with the auth table.

2. Add the source channel Field source of the joined table to the gridview.

<?= GridView::widget([// other codes'columns' => [// other columns'auth.source',]]); ?>

Some people may have doubts. Didn't I say there is no association above? How can I directly use auth. source?

Run debug to check the actual query.

We will find that there are many operations like select * from 'auth 'where uid = xxx;. If your page contains 20 pieces of data by default, there will be 20 similar queries.

Let's first figure out what happened?

In fact, this is the basic knowledge of php. When a nonexistent member variable is read and written to an object, the _ get () _ set () magic function is automatically called. Yii also uses this to implement it!

This operation is almost the same as that of most people in the gridview encapsulation method to obtain the associated table data,! The query of 20 SQL statements significantly increases the overhead. How nice is the left join operation!

3. Optimize SQL

What we need to optimize is:

Change 20 SQL statements to 1 SQL statement

Only obtain the fields required for the joined table

Some people are clamoring. Here is the yii built-in operation. How can we optimize it? We return to the data source acquisition and find that the user list data is provided through the search method of the userSearch model.

That is to say, our data query has not actually performed Association Table query! In this case, we add the associated query in UserSearch.

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

Let's refresh the user list page. Then, we found that two SQL statements caught our attention through debug analysis.

SELECT 'user '. *, 'auth '. 'source' FROM 'user' left join 'auth' ON 'user '. 'id' = 'auth '. 'uid' LIMIT 20 SELECT * FROM 'auth 'WHERE 'user _ id' IN (20 UIDs );

That is to say, I have achieved the goal of optimizing SQL. Through the debug analysis, I found that the database query time is much less.

4. Add query for joined table fields

The search model in the gridview is also implemented through the searchModel. The model uses rules to control which fields can be searched and which fields cannot be searched.

Now we need to add the source of the joined table to be searchable. Therefore, we define a property source in searchModel and add it to rules.

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

Next, modify auth. source in the gridview.

// 'Auth. source ', ['attribute' => 'source', 'value' => 'auth. source ', 'label' => 'Channel source',],

At this point, the interface is OK. To implement the search in the program, we can add the new source condition to the data source.

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

The following describes the usage of ActiveRecord in yii.

1. Convert objects to Arrays

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

Because ActiveRecord is not a simple array, json_encode cannot be used directly; otherwise, the information is incomplete.

Solution: $ model. toArray (); then it becomes a simple array and json_encode can be implemented.

2. Get the id of ActiveRecord by name or other fields.

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

I used to use the following methods ):

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

3. Understanding of model

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

The above section describes how to implement Yii2 ActiveRecord multi-Table Association and multi-Table Association search. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!