Making column searchable and sortable in GrindView when related to another table in DataBase

Yii 2.0 General Discussions
1

Hello,

I’m currently working on a GridView in Yii2 where one column in particular is a relation of “has many”. Currently i’m showing the column information using the following code in the GridView widget:




'columns' => [

            'email:email',

            'username',

            'signup_time:datetime',

            [

                'header' => Yii::t('app','Role(s)'),

                'content' => function ($model, $key, $index, $column){

                    $roles = $model->getRoles()->all();

                    $string = "";

                    if(count($roles)>0){

                        if(count($roles)>1){

                            $string .="| ";

                            foreach ($roles as $role) {

                                $string .= $role->getAttribute('name')." | ";

                            }

                        }else{

                            $string .= $roles[0]->getAttribute('name');

                        }

                    }

                    return $string;

                }

            ],

            [

                'class' => 'yii\grid\ActionColumn',

                'template' => '{view} {update} {delete}',

            ],

        ],

on the model side the relation is defined as follows:




/**

     * @return \yii\db\ActiveQuery

     */

    public function getRoles()

    {

        return $this->hasMany(TcWebRole::className(), ['id' => 'role_id'])->viaTable('user_role', ['user_id' => 'id']);

    }

this resulting GridView is as shown in the image below

7385

grid.png

What i need to do is make the column ‘Role(s)’ searchable and sortable like the other fields in the GridView. I assume this needs to be done via the search model but not sure how to go about doing that. Would appreciate any suggestions on how i should proceed.

thanks in advance

2

Hi natral, if you wanna be able to apply filters in your gridview by a relational table attribute, you should be using joinWith in your searchModel query. Like this:




...

$query = Users::find()->joinWith(['roles']);

...

Then you should declare a public property to store the value of the user input in your UsersSearchModel, and set that property as "safe" in your rules, like this:




class UsersSearch extends Users

{


public $role;


public function rules()

    {

        return [

            ...

            ['role', 'safe'],

            ...

        ];

    }


...


}

Now you have to set the attribute in your gridview for your relation column like this:




[

                'header' => Yii::t('app','Role(s)'),

                'attribute' => 'role',

                'content' => function ($model, $key, $index, $column){

                    $roles = $model->getRoles()->all();

                    $string = "";

                    if(count($roles)>0){

                        if(count($roles)>1){

                            $string .="| ";

                            foreach ($roles as $role) {

                                $string .= $role->getAttribute('name')." | ";

                            }

                        }else{

                            $string .= $roles[0]->getAttribute('name');

                        }

                    }

                    return $string;

                }

            ],

The you have to define the filter for that attribute in your searchModel.




...

$query->andFilterWhere(['like', 'tc_web_role.name', $this->role]);

...

For sorting the gridview you need to set the sort for this attribute in your searchModel as well.




$dataProvider->sort->attributes['role'] = [

        'asc' => ['tc_web_role.name' => SORT_ASC],

        'desc' => ['tc_web_role.name' => SORT_DESC],

        ];

Assuming "tc_web_role" is the table name for your table, and "name" is the column name in your table.

3

Your roles relation should be a hasMany so you don’t have to do all of that messy stuff. You’d still do the join with like gonzalo said except this is what you’d be joining.




public function getRoles{

  return $this->hasMany(Roles::className(),['role_id','id']);

}

You should put your translation in the attributeLabels() function in your model so you don’t have to do it on the column and each time you call the attribute you’d just do it once.




attributeLabels(){

return =>[

'role'=>Yii::t('app','Role(s)')

];

}

you could also do something like this for your column




[

 'attribute'=>'role',

 'content'=>function($model){

    $roles = $model->roles;

    if(empty($roles) && is_array($roles)){

      return explode(' | ', $roles);

    }

    return Yii::$app->formatters->nullDisplay;

 }

]

you don’t need an else because the else is that there isn’t a related record so just return the gridview’s null value. You also can just use the php explode function because if it’s only one attribute it won’t append the separator.