PHP, Yii2 GridView filtering on relational value

Question

Following on from this:

Yii2 how does search() in SearchModel work?

I would like to be able to filter a GridView column of relational data. This is what I mean:

I have two tables, TableA and TableB. Both have corresponding models generated using Gii. TableA has a foreign key to a value in TableB, like this:

TableA
attrA1, attrA2, attrA3, TableB.attrB1

TableB
attrB1, attrB2, attrB3

attrA1 and attrB1 are the primary keys of their corresponding tables.

Now, I have a Yii2 GridView of attrA2, attrA3 and attrB2. I have a working filter on attrA2 and attrA3 so that I can search on column values. I also have a working sort for these two columns too - by just clicking on the column header. I would like to be able to add this filtering and sorting on attrB2 too.

My TableASearch model looks like this:

public function search($params){
    $query = TableA::find();
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

    $this->addCondition($query, 'attrA2');
    $this->addCondition($query, 'attrA2', true);
    $this->addCondition($query, 'attrA3');
    $this->addCondition($query, 'attrA3', true);

    return $dataProvider;
}

In my TableA model, I set the related value like this

    public $relationalValue;

public function afterFind(){
    $b = TableB::find(['attrB1' => $this->attrB1]);
    $this->relationalValue = $b->relationalValue;
}

Although it is probably not the best way of doing this. I think I have to use $relationalValue somewhere in my search function but I'm not sure how. Similarly, I would like to be able to sort by this column too - just like I can for attrA2 and AttrA3 by clicking on the header link`. Any help would be appreciated. Thanks.

Answer 1

This is based on the description in the guide. The base code for the SearchModel comes from the Gii code generator. This is also assuming that $this->TableB has been setup using hasOne() or hasMany() relation. See this doc.

1. Setup search model

In TableASearch model add:

public function attributes()
{
    // add related fields to searchable attributes
    return array_merge(parent::attributes(), ['TableB.attrB1']);
}

public function rules() 
{
    return [
        /* your other rules */
        [['TableB.attrB1'], 'safe']
    ];
}

Then in TableASearch->search() add (before $this->load()):

$dataProvider->sort->attributes['TableB.attrB1'] = [
      'asc' => ['TableB.attrB1' => SORT_ASC],
      'desc' => ['TableB.attrB1' => SORT_DESC],
 ];

$query->joinWith(['TableB']); 

Then the actual search of your data (below $this->load()):

$query->andFilterWhere([
    'like',
    'TableB.attrB1',
     $this->getAttribute('TableB.attrB1')
]);

2. Configure GridView

Add to your view:

echo GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        /* Other columns */
       'TableB1.attrB1',
        /* Other columns */        
     ]
]);

Answer 2

Filtering a gridview by a column is damn easy in Yii 2.0. Please add the filter attribute to a gridview column having lookup values, as under:

[
        "class" => yii\grid\DataColumn::className(),
        "attribute" => "status_id",
        'filter' => ArrayHelper::map(Status::find()->orderBy('name')->asArray()->all(), 'id', 'name'),
        "value" => function($model){
            if ($rel = $model->getStatus()->one()) {
                return yii\helpers\Html::a($rel->name,["crud/status/view", 'id' => $rel->id,],["data-pjax"=>0]);
            } else {
                return '';
            }
        },
        "format" => "raw",
],