_php instance of YII2 ActiveRecord Multi-Table association and multi-Table association Search

The ActiveRecord of Yii is the class that deals with the database, the M (model layer) in MVC, and the Orm O (Object).

A commonplace question. Recently through the group of feedback, feel that many people still do not understand this problem. Let's get this over with today and see how the Yii2 ActiveRecord is a multiple-table association and how to optimize the association.

Scenario Requirements:

Let's say we have a user table and a user channel table Auth, and the two data tables are one-to-one associated with user.id and Auth.uid. It is now necessary to display the source sources for the Auth table in the user list, and the channel can be searched.

First we'll build the model and operations related to the user and Auth series via GII. Details are not available here, and the actions on GII can refer to XXX

I'm going to look at some important steps:

1, find the User table corresponding AR model class common\models\user.php, in this class of files associated auth table

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

After it is set up, it does not mean that two data tables are automatically associated! We accessed the User List page (the list page was built in GII, we haven't done it at the moment), and it's not hard to see through the debug database queries that the actual query doesn't correlate auth tables

2, add the associated table in the GridView Source Channel field sources

<?= Gridview::widget ([/
/Other codes
' columns ' => [
//Other columns
' Auth.source ',
]
]);?>

Some students feel the doubt, the above is not said not to be related, how can this direct use of Auth.source?

Don't worry, we'll open debug and look at the actual query.

We will find that there are many such operations as SELECT * from ' auth ' where uid = xxx, and if your paging defaults to 20 data, there will be 20 similar query.

Let's figure out what happened.

This is actually a basic knowledge of PHP. The __get () __set () Magic function is invoked automatically when a nonexistent member variable is read and written to the object. Yii is also using this to achieve it!

The operation is almost consistent with the way most people encapsulate the data in the GridView to get the associated table, but! The 20-SQL query significantly adds a lot of overhead. If this is a left JOIN operation how good!

3. Optimizing SQL

What we need to optimize is:

20 SQL changes to 1 SQL

Get only the fields that are required by the associated table

There are students to shout, here is the operation of Yii, how to optimize? We went back to the data source and found that the data for the user list was provided through the Usersearch model search method.

In other words, our data query does not actually go to the related table query! In that case, we'll add the associated query to Usersearch.

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

We're going to refresh the user list page, and then we'll find out from the debug analysis that there are two of SQL that caught our attention.

Select ' User '. *, ' auth '. ' Source ' from ' user ' left JOIN ' auth ' in ' user '. ' id ' = ' auth '. ' uid ' LIMIT
SELECT * from ' AU Th ' WHERE ' user_id ' in (20 uid);

In other words, I have reached the goal of optimizing SQL, through the debug analysis found that DB query time is much less.

4. Add Query to associated table field

The search model in the GridView is also implemented through Searchmodel, which controls which fields are searchable and which fields are not searchable by rules.

We now need to increase the source search for the associated table, 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 modify the Auth.source in the GridView.

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

Here we have the interface is OK, to achieve the program search is still a step, we have access to the data sources add new source conditions can be

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

Here are some of the things you can do to supplement ActiveRecord in Yii

1, an array of objects

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

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

Solution: $model. ToArray (); this becomes a simple array and can be json_encode.

2, get the ActiveRecord ID directly by name or other field.

The way I used to use it all the time was (now I find it very dirt):

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

3, the understanding of model

$accModel = Call_user_func (Array (activerecordname, ' model ')); 
$model  

The above is a small set to introduce the YII2 ActiveRecord multiple table correlation and the realization of a number of related search knowledge, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!