Yii2 implements cross-mysql database related query sorting function code

This article mainly introduces an example of Yii2's implementation of cross-mysql database related query sorting function. It has certain reference value and interested friends can refer to it.

Background: There are two databases on a mysql server (note: the two databases must be on the same mysql server):

There is a user table in memory (storing regular data tables) (Record user information)

There is a user_stat in memory_stat (storage statistical data table) (Record user statistical data)

Now the user_stat in the GridView list generated by the user table is displayed Statistical data

You only need to add an association in the User model class

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

You can use it in GridView like this Display statistical data

<?= GridView::widget([
 &#39;dataProvider&#39; => $dataProvider,
 &#39;columns&#39; => [

  //其他列
  
  [
   &#39;label&#39; => &#39;统计数据&#39;,
   &#39;value&#39; => function($model){
    return isset($model->stat->data) ? $model->stat->data : null;
   }
  ],
  
  //其他列
 ],
]); ?>

Now a requirement has been added to sort and filter statistical data 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'],
  //其他列
 ];
}/*}}}*/

public function search($params, $onlyActiveUsers = false)
{
 $query = User::find();
 $query->joinWith(['stat']);

 $dataProvider = new ActiveDataProvider([
  'query' => $query,
  'sort' => [
   'attributes' => [
    //其他列
    
    'data' => [
     'asc' => [UserStat::tableName() . '.data' => SORT_ASC],
     'desc' => [UserStat::tableName() . '.data' => SORT_DESC],
    ],
    
    //其他列
   ],
   'defaultOrder' => [
    'id' => SORT_DESC,
   ],
  ],
  'pagination' => [
   'pageSize' => 50,
  ],
 ]);

 $this->load($params);

 if (!$this->validate()) {
  $query->where('0=1');
  return $dataProvider;
 }

 $query->filterWhere([
 
  //其他列
  
  UserStat::tableName() . '.data' => $this->data
 ]);

 return $dataProvider;
}

In GridView You can use this to display statistical data and sort it

<?= GridView::widget([
 &#39;dataProvider&#39; => $dataProvider,
 &#39;columns&#39; => [

  //其他列
  
  [
   &#39;label&#39; => &#39;统计数据&#39;,
   &#39;attribute&#39; => &#39;data&#39;,
   &#39;value&#39; => function($model){
    return isset($model->stat->data) ? $model->stat->data : null;
   }
  ],
  
  //其他列
 ],
]); ?>

Add the following columns to the search form to filter

<?php $form = ActiveForm::begin(); ?>
//其他列 

<?= $form->field($model, &#39;data&#39;)?>

//其他列
<p class="form-group">
 <?= Html::submitButton(&#39;Search&#39;, [&#39;class&#39; => &#39;btn btn-primary&#39;]) ?>
</p>

<?php ActiveForm::end(); ?>

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

Then such an error will be reported:

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

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

Copy code 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;

Yii2 will not add the database name before the statement by default when converting it into a SQL statement, so mysql will default to this table when executing the sql statement. under the memory database.

Copy code The code is as follows:

select a.*,b.* from memory.user as a, memory.user_stat as b where a.id=b.user_id;

So the above error message appeared.

So, how to solve this problem?

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

// 默认是这样的
public static function tableName()
{
 return 'user_stat';
}

public static function getDb()
{
 return Yii::$app->get('dbStat');
}

// 只需要在表明前添加数据库名
public static function tableName()
{
 return 'memory_stat.user_stat';
}

public static function getDb()
{
 return Yii::$app->get('dbStat');
}

// 为了提高代码稳定性，可以这样写
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');
}

That’s it I hope that the entire content of this article will be helpful to everyone's learning, and I also hope that everyone will support the PHP Chinese website.

For more Yii2 implementation of cross-mysql database related query sorting function code related articles, please pay attention to the PHP Chinese website!