Regarding GridView Filter in yii2

Question

I have a table like this with 5 columns.

TableName
-Column1
-Column2
-Column3
-Column4
-Column5

I had merged them to display them in grid view as single column.

Question How can i make filter condition query to search them based on user input.? Eg.User types something as input, it have search from all the 5 columns and return the result based on the search input.(Sorting works fine, please help me with filtering)

If someone could helpme it would be great, Thanks.

UPDATE:

$query->andFilterWhere(['ilike', '"x"."y"', $this->variantName])     ->andFilterWhere(['"a"."b"' => $this->ClassId])
->andFilterWhere(['"c"."d"' => $this->FamilyId]) 
->andFilterWhere(['"e"."f"' => $this->PlatformId]) 
->andFilterWhere(['ilike', '"g"."h"', $this->subFamilyName])

This is how my old model looks like the fields with familyId,classId,PlatformId are integer and subfamilyname,variantname are text.

Modified:

$query->andFilterWhere(['or',
                ['ilike', '"x"."y"', $this->Combo],
                ['"a"."b"' => $this->Combo],
                ['"c"."d"' => $this->Combo],
                ['"e"."f"' => $this->Combo],
                ['ilike', '"g"."h"', $this->Combo],
                ])

UPDATE 2: This is how the query looked before merging columns.

->andFilterWhere(['ilike', '"storeNames"."variantName"', $this->variantName])
              ->andFilterWhere(['"storeNames"."classId"' => $this->malwareClassId])
              ->andFilterWhere(['"storeNames"."familyId"' => $this->malwareFamilyId])
              ->andFilterWhere(['"storeNames"."platformId"' => $this->malwarePlatformId])
             ->andFilterWhere(['ilike', '"storeNames"."subFamilyName"', $this->subFamilyName]);

Answer 1

I will add an example that uses a countries table to display data with GridView.

In order to accomplish what you need you have to take the following steps.

1. Create a custom attribute/field inside your SearchModel.
2. Add the field to gridview column.
   • Define the field as safe inside the rules.
3. update the search() function inside the SearchModel to search and compare based on the new custom field.

Let's say I have a Countries table with 2 models

1. Countries
2. CountriesSearch

The countries table has name and code field and i want to show country name and code inside a single column like below.

and i want that the filter field above on the gridview should be able to search if i type name or code any one of them.

Add field name $combo in the CountrieSearch

public $combo

add the field to the rules as safe

 public function rules() {
        return [
            [ [ 'id' ] , 'integer' ] ,
            [ [ 'name' , 'code' , 'combo' ] , 'safe' ] ,
        ];
    }

Add the new field to the gridview

<?= GridView::widget([
'dataProvider' => $dataProvider,
'filterModel' => $searchModel,
'columns' => [
    ['class' => 'yii\grid\SerialColumn'],

    [
        'attribute'=>'combo',
        'label'=>'Name/Code',
        'value'=>function($model){
            return '<span class="label label-success">'.$model->name.'</span><span class="label label-info">('.$model->code.')</span>';
        },
        'format'=>'raw',
    ],

    ['class' => 'yii\grid\ActionColumn'],
],
]); ?>

Then update the search function and add the following lines just before you are returning the $dataProvider inside the search() method

 $query->andFilterWhere ( [ 'OR' ,
            [ 'like' , 'name' , $this->combo ],
            [ 'like' , 'code' , $this->combo ],
        ] );

Hope this helps you out