YII2 implementation of cross-MySQL database association query sorting function

Encounter a project, need to find a lot of information across the table, tidy up, easy to use again later

Background: On a MySQL server ( Note: Two databases must be on the same MySQL server ) There are two databases:

Memory (store general data table) with a user table (record user information)

Memory_stat (Storage statistics table) has a user_stat (log user statistics)

Statistics in User_stat are now displayed in the GridView list generated by the user table

• You only need to add an association in the user's model class.

 Public function Getstat () {    return$this->hasone (Userstat::classname (), [' user_id ' = ' = ' id ' ) ]);}

This can be used in the GridView to show statistics

<?= GridView::Widget ([' Dataprovider ' =$dataProvider, ' columns ' = [        //Other Columns                [            ' Label ' = ' statistics ', ' value ' = =function($model){                return isset($model-Stat->data)?$model-Stat->data:NULL; }        ],//Other Columns],]); ?>

Or so

<?= GridView::Widget ([' Dataprovider ' =$dataProvider,//' Filtermodel ' = $searchModel,' Columns ' = [//[' class ' = ' Yii\grid\serialcolumn '],                     [                                                  ' label ' + ' statistics ', ' attrube ' + ' data '//Enter the corresponding' Value ' = ' stat.data '                     ],                                                            [' Class ' = ' Yii\grid\actioncolumn '],                ],            ]); ?>

Now adds a need to sort and filter statistics in the user GridView list

If the user and User_stat tables are in the same database, we can do this:

• Usersearch:

 Public $data; Public functionrules () {/*{{{*/    return [        [' Data '], ' integer ',//Other Columns    ];}/*}}}*/ Public functionSearch$params,$onlyActiveUsers=false){    $query= User::find (); $query->joinwith ([' Stat ']); $dataProvider=NewActivedataprovider ([' Query ' =$query, ' sort ' = [            ' Attributes ' = [                //Other Columns' Data ' = [                    ' ASC ' = [Userstat::tablename (). '. Data ' = Sort_asc], ' desc ' = [Userstat::tablename (). '. Data ' = Sort_desc],                ],//Other Columns], ' defaultorder ' = [                ' id ' = Sort_desc,            ],        ], ' pagination ' = [            ' PageSize ' = 50,        ],    ]); $this->load ($params); if(!$this-Validate ()) {        $query->where (' 0=1 '); return $dataProvider; }    $query-Filterwhere ([//Other ColumnsUserstat:: TableName (). '. Data ' =$this-data]); return $dataProvider;}

The search form can be filtered by adding the following

$form = Activeform::begin ();?>//$form->field ($model, ' data ')? >// Other column class= "Form-group" >    <?= Html::submitbutton (' Search ', [' class ' = = ' Btn btn-primary '])? ></div><?php ActiveForm::end();?>

So, how to solve this problem?

• In fact it is very simple, only need to rewrite User_stat model class under the TableName () method can be.

 //   Public  static  function   TableName () { return  ' user_stat '  public  static  function   Getdb () { return  yii:: $app ->get (' Dbstat '  

// just add the database name before you indicate  Public Static function TableName () {    return ' Memory_stat.user_stat ';}  Public Static function Getdb () {    return Yii::$app->get (' Dbstat ');}

Reprinted: 1190000008107267

Invasion and deletion!!!

YII2 implementation of cross-MySQL database association query sorting function