YII2 ActiveRecord Multi-Table association and implementation of multi-table association search, yii2activerecord_php Tutorial

YII2 ActiveRecord Multi-Table association and the implementation of multi-table association search, Yii2activerecord

A commonplace question. Recently, through the feedback in the group, I feel that many people still do not understand the problem. To get this problem clear today, let's see how Yii2 ActiveRecord is associated with multiple tables and how to optimize this association.

Scenario Requirements:

Suppose we have a user table and a user channel table Auth, and two data tables are one-to-one by User.ID and Auth.uid. You now need to show source channel sources for the Auth table in the user list, and the channel is searchable.

First we generate the user and Auth series related model and operations through the GII. We do not elaborate here, but we can refer to XXX for the operation of the GII

I'm going to look at some important steps:

1, find the User table corresponding to the AR model class common\models\user.php, in the class file associated auth table

/* * *  Associated auth table
*/  Public function GetAuth () {    ///  Hasone requires a return of two parameters the first parameter is the class name of the associated table The second argument is the association relationship between the two tables     //Here The UID is the Auth Table Association ID, The UID ID of the associated user table is the primary key ID of the current model    return$this->hasone (Common\models\auth::classname () , [' uid ' = ' id ']);

Once set up, it doesn't mean that two datasheets are automatically associated! We visit the User List page (which is generated using GII, we have not operated at this time), debug view database queries is not difficult to find, the actual query is not associated auth table

2. Add the Source channel field of the associated table in the GridView source

 
  Widget ([    // other codes    ' columns ' = +        [//  Other Columns        ' Auth.source ',    ?>

Have classmates feel doubt, above not said did not carry out association, this how can direct use Auth.source?

Don't worry, we'll turn on debug to see the actual query.

We will find that there are a lot of things like select * from ' auth ' where uid = xxx; If your paging defaults to 20 data, there will be 20 similar query.

Let's figure out what's going on.

This is actually a basic knowledge of PHP. The __get () __set () Magic function is automatically called when a nonexistent member variable is read and written to an object. Yii also makes use of this point to implement it!

This operation is almost consistent with most people getting the associated table data in the GridView encapsulation method, but! 20 SQL queries have significantly increased the overhead. If this is the left JOIN operation how good!

3. Optimize SQL

What we need to optimize is:

20 SQL Variable 1 SQL

Get only the fields required for the associated table

There are students to yell, here is the operation of Yii comes, how to optimize? We go back to the data source and find that the data for the user list is provided through the search method of the Usersearch model.

In other words, our data query actually does not go to the association table query! That being the case, we'll just add the Usersearch query.

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

Let's refresh the user list page and find out that two SQL has caught our attention with the debug analysis.

JOIN ' auth ' on ' user '. ' id ' = ' auth '. ' uid ', 'LIMIT ' from ' auth ' WHERE ' user_id ' in (20 uid);

That is to say, I have achieved the purpose of optimizing SQL, through the debug analysis found that DB query time is much less.

4. Add Query to Related table fields

The search model in the GridView is also implemented by Searchmodel, which controls which field can be searched and which field is not searchable through rules.

We now need to increase the source of the associated table to be searchable, so we define a property source in Searchmodel and add it to the rules

 Public $source ;  Public function rules () {    return  [    // other rules        [' Source ', ' Safe '],    ];}

Then we'll change the Auth.source in the GridView.

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

Here we are OK on the interface, to achieve a program on the search is one step further, we get the source of the data sources with the new source conditions can be

$query-andfilterwhere ([    // other params    $this- >source,]);

http://www.bkjia.com/PHPjc/1140067.html www.bkjia.com true http://www.bkjia.com/PHPjc/1140067.html techarticle yii2 activerecord Multi-Table association and the implementation of multi-table association search, Yii2activerecord a commonplace problem. Recently through the group feedback, I think many people still do not understand this ...

