yii2 gridview filter on related column

Question

I have joined 2 tables on my model search with

$query = Surveys::find()->select('surveys.*,regions.name AS region_name, published.description as published_name');
        $query->joinWith(['region']);
        $query->joinWith(['published0']);

I have now added sorting for the 2 extra columns

$dataProvider->setSort([
            'attributes' => [
                'name',
                'description',
                'survey_type',
                'published_name' => [
                    'asc' => ['published.description' => SORT_ASC],
                    'desc' => ['published.description' => SORT_DESC],
                    'label' => 'Published',
                    'default' => SORT_ASC
                ],
                'region_name' => [
                    'asc' => ['regions.name' => SORT_ASC],
                    'desc' => ['regions.name' => SORT_DESC],
                    'label' => 'Region'
                ]
            ]
        ]);

I am not sure how to do filtering for these 2 columns. following is what i have

 $query->andFilterWhere([
            'survey_id' => $this->survey_id,
            'published' => $this->published,
            'date_added' => $this->date_added,
            'total_length' => $this->total_length,
            'region_id' => $this->region_id,
            'shrink' => $this->shrink,
            'country_id' => $this->country_id,
            'region_name' => $this->region_name,
            'published_name' => $this->published_name
        ]);

        $query->andFilterWhere(['like', 'name', $this->name])
            ->andFilterWhere(['like', 'description', $this->description])
            ->andFilterWhere(['like', 'guid', $this->guid])
            ->andFilterWhere(['like', 'year_acquired', $this->year_acquired])
            ->andFilterWhere(['like', 'year_processed', $this->year_processed])
            ->andFilterWhere(['like', 'survey_type', $this->survey_type])
            ->andFilterWhere(['like', 'processing_type', $this->processing_type])
            ->andFilterWhere(['like', 'center_point', $this->center_point])
            ->andFilterWhere(['like', 'regions.name', $this->region_name])
            ->andFilterWhere(['like', 'published.description', $this->published_name]);

I have also included the public variables in the Surveys model

 public $region_name;
     public $published_name;

On my gridview the 2 columns appear and i can sort them however there is no input box for filtering. how can i filter this?

Jørgen Jørgen · Accepted Answer · 2015-04-22T13:08:12

The variables must be filtered trough at least one rule.

Try adding this to your search model:

rules()
{
    return [
        ...
        [['region_name', 'published_name'], 'string', 'max' => 255]
    ];
}

From the docs:

When this property is set, the grid view will enable column-based filtering. Each data column by default will display a text field at the top that users can fill in to filter the data.

Note that in order to show an input field for filtering, a column must have its yii\grid\DataColumn::$attribute property set or have yii\grid\DataColumn::$filter set as the HTML code for the input field.

When this property is not set (null) the filtering feature is disabled.

yii2 gridview filter on related column

1 Answers