Yii2實現跨mysql資料庫關聯查詢排序功能

白菜1031發表於2019-02-16
MySql資料庫排序

背景：在一個mysql伺服器上（注意：兩個資料庫必須在同一個mysql伺服器上）有兩個資料庫：

memory (儲存常規資料表) 中有一個 user 表(記錄使用者資訊)

memory_stat (儲存統計資料表) 中有一個 user_stat (記錄使用者統計資料)

現在在 user 表生成的 GridView 列表中展示 user_stat 中的統計資料

  • 只需要在User的model類中新增關聯

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

  • 在GridView就可以這樣使用來展示統計資料

<?= GridView::widget([
    `dataProvider` => $dataProvider,
    `columns` => [

        //其他列
        
        [
            `label` => `統計資料`,
            `value` => function($model){
                return isset($model->stat->data) ? $model->stat->data : null;
            }
        ],
        
        //其他列
    ],
]); ?>

現在增加了一個需求，需要在user GridView 列表中對統計資料進行排序和篩選

若 user 和 user_stat 表在同一個資料庫下我們可以這樣做：

  • 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;
}

  • 在GridView就可以這樣使用來展示統計資料,就可以排序了

<?= GridView::widget([
    `dataProvider` => $dataProvider,
    `columns` => [

        //其他列
        
        [
            `label` => `統計資料`,
            `attribute` => `data`,
            `value` => function($model){
                return isset($model->stat->data) ? $model->stat->data : null;
            }
        ],
        
        //其他列
    ],
]); ?>

  • search 表單中新增以下列就可以篩選了

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

<?= $form->field($model, `data`)?>

//其他列
<div class="form-group">
    <?= Html::submitButton(`Search`, [`class` => `btn btn-primary`]) ?>
</div>

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

然而現實是殘酷的， user 和 user_stat 表並在同一個資料庫下。

於是就會報出這樣一個錯誤：

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

要在兩個資料庫（同一臺伺服器）上進行關聯資料查詢，純SQL語句如下：

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

Yii2轉化成 SQL 語句時預設不會在表明前新增資料庫名，於是mysql在執行sql語句時就會預設此表在memory資料庫下。

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

於是就出現了以上報錯資訊。

那麼，如何來解決這個問題呢？

  • 其實很簡單，只需要重寫 user_stat 的 model 類下的 tableName() 方法就可以了。

// 預設是這樣的
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`);
}

相關文章