Yii 2 Gridview 实现 跨表关联搜索和关联排序

{ 1. 数据库结构 }

bill{b_id, b_client_id, b_amount, b_status_id, b_creationDate}

client{c_id, c_name, c_phone}

FK bill.b_client_id = client.c_id

{ 2. 场景 }

可以通过手机号，查询用户订单列表，同时显示订单详情(金额等)

{ 3. Model }

//Bill.php
 * @property Client $bClient

public $b_clientPhone;
public $b_clientName;

/**
 * @return \yii\db\ActiveQuery
 */
public function getBClient()
{
    return $this->hasOne(Client::className(), ['c_id' => 'b_client_id']);
}



//BillSearch.php


public function search($params)
{
    $query = Correction::find();
    $query->joinWith('bClient');

    // add conditions that should always apply here

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
    
    $dataProvider->setSort([
        'defaultOrder' => ['b_creationDate'=>SORT_DESC],
        'attributes' => [
            'b_creationDate',
            'b_status_id',
            'b_clientPhone' => [
                    'asc' => ['client.c_phone' => SORT_ASC],
                    'desc' => ['client.c_phone' => SORT_DESC],
                    'label' => 'State'
            ],
            'b_clientName' => [
                    'asc' => ['client.c_name' => SORT_ASC],
                    'desc' => ['client.c_name' => SORT_DESC],
                    'label' => 'State'
            ],
        ]
    ]);

    //OR
    $dataProvider->sort->attributes['b_clientName'] = [
        'asc' => ['client.c_name' => SORT_ASC],
        'desc' => ['client.c_name' => SORT_DESC],
        'label' => 'State'
    ];

    ...

    $query->andFilterWhere(['like', 'b_creationDate', $this->b_creationDate])
        ->andFilterWhere(['like', 'c_name,', $this->b_clientName])
        ->andFilterWhere(['like', 'c_phone', $this->b_clientPhone])
    ...
}

{ 4. Controller }

public function actionIndex()
{   
    $searchModel = new BillSearch();
    $queryParams = Yii::$app->request->queryParams;
    $dataProvider = $searchModel->search($queryParams);

    return $this->render('index', [
        'searchModel' => $searchModel,
        'dataProvider' => $dataProvider,
    ]);
}

{ 5. View }

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
//  'rowOptions' => ['style'=>'text-align:center;vertical-align: middle;'],
    'columns' => [
//  ['class' => 'yii\grid\SerialColumn'],

        [
            'attribute'=>'b_id',
            'value'=>function ($model) { return $model->b_id; },
            'filter'=>false,
            'format'=>'raw',
            'label'=>'Submitter',
            'headerOptions' => ['width' => '330px'],
            'contentOptions' => ['style'=>'vertical-align: middle;'],
        ],
        [
            'attribute'=>'b_clientPhone',
            'value'=>function ($model) { return $model->bClient->c_phone; },
            'label'=>'Name',
            'contentOptions' => ['style'=>'vertical-align: middle;'],
        ],
        [
            'attribute'=>'b_clientName',
            'value'=>function ($model) { return $model->bClient->c_name; },
            'label'=>'Name',
            'contentOptions' => ['style'=>'vertical-align: middle;'],
        ],
        'b_creationDate'
        'b_status_id'

{ 6. One More Thing }

简单来说就是利用relation告诉Yii如何拼接出需要的SQL，同时适合Gridview的使用。