Yii2 implements the cross-mysql database Association query and sorting function code

This article mainly introduces the example of Yii2's cross-mysql database Association query sorting function, which has some reference value. if you are interested, you can refer to it. This article mainly introduces the example of Yii2's cross-mysql database Association query sorting function, which has some reference value. if you are interested, you can refer to it.

Background: On a mysql server (note: the two databases must be on the same mysql Server), there are two databases:

Memory (storing regular data tables) has a user table (recording user information)

Memory_stat (storage statistics table) has a user_stat (record user statistics)

The statistics in user_stat are now displayed in the GridView list generated by the user table.

You only need to add an association to the User's model class.

public function getStat(){ return $this->hasOne(UserStat::className(), ['user_id' => 'id']);}

This can be used in the GridView to display statistics.

 $ DataProvider, 'columns' => [// Other columns ['label' => 'statistics', 'value' => function ($ model) {return isset ($ model-> stat-> data )? $ Model-> stat-> data: null;}], // Other columns],]);?>

Now, you need to sort and filter the 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 function rules () {/* {*/return [['data'], 'integer'], // Other columns];} /* }}*/public function search ($ params, $ onlyActiveUsers = false) {$ query = User: find (); $ query-> joinWith (['Stat']); $ dataProvider = new ActiveDataProvider (['query' => $ query, 'sort '=> ['bubuckets' => [// 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 columns UserStat:: tableName (). '. data '=> $ this-> data]); return $ dataProvider ;}

In the GridView, you can use this function to display statistics and sort them.

 $ DataProvider, 'columns' => [// Other columns ['label' => 'statistics', 'attribute' => 'Data ', 'value' => function ($ model) {return isset ($ model-> stat-> data )? $ Model-> stat-> data: null;}], // Other columns],]);?>

You can filter the following items in the search form.

 // Other columns
 Field ($ model, 'data')?> // Other columns
 
  'Btn btn-primary '])?>
 

However, the reality is cruel. the user and user_stat tables are in the same database.

The following error is reported:

SQLSTATE [42S02]: Base table or view not found: 1146 Table 'memory. user_stat 'doesn' t exist
The SQL being executed was :...

To query associated data on two databases (the same server), the pure SQL statement is as follows:

The code is as follows:

Select a. *, B. * from memory. user as a, memory_stat.user_stat as B where a. id = B. user_id;

When Yii2 is converted to an SQL statement, the database name is not added before the statement is displayed by default. Therefore, when mysql executes an SQL statement, the table is stored in the memory database by default.

The code is as follows:

Select a. *, B. * from memory. user as a, memory. user_stat as B where a. id = B. user_id;

The above error message is displayed.

So how can we solve this problem?

In fact, it is very simple. you only need to override the tableName () method under the model class of user_stat.

// The default value is public static function tableName () {return 'user _ stat';} public static function getDb () {return Yii :: $ app-> get ('dbstat ');}

// You only need to add the database name public static function tableName () {return 'memory _ stat. user_stat ';} public static function getDb () {return Yii: $ app-> get ('dbstat ');}

// To improve code stability, you can write public static function tableName () {preg_match ("/dbname = ([^;] +)/I", static: getDb () -> dsn, $ matches); return $ matches [1]. '. user_stat ';} public static function getDb () {return Yii: $ app-> get ('dbstat ');}

The above is all the content of this article. I hope it will help you learn and support PHP.

For more information about the code of Yii2 for cross-mysql database Association query and sorting function, see PHP!