Yii Framework 2.0 GridView and data from the join table

Question

I have two database tables 'user' and 'role'. I used Yii framework 2.0 Gii to create CRUD with User model and UserSearch model. By default, Gii uses GridView::widget for the index page for the 'user' model.

In the search($params) method inside of the UserSearch model, I used the following code to join the above tables together

$query = User::find()->with('role');

Everything works fine with the query.

By default Gii does not include the data from the joined table 'role' in the GridView::widget inside of the views/user/index.php page. With the join query above I could retrieve data from both tables. In the views/user/index.php page I have injected the GridView::widget with the following code so that it also includes the data and column names from the joined table (role).

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        'userid',
        'username',
        'role.role_name',
        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

Everything works fine with the role data 'role_name included in the GridView::widget. But the problem is that there is no search box for the role_name. The GridView::widget creates search box for the User properties only. Is there any way to add search box for the properties of the joined table 'role' because I also would like to search through 'role_name' as well as through other properties of the User model.

Solution 1

Try this way:

In your UserSearch model add

UserSearch extends ...
{
    public $roleFilterInputName; //the name of the filter search input

    //important
    function rules()
    {
        //add roleFilterInputName as safe
        return [
            [['xxx', 'roleFilterInputName'], 'safe'], //!!!!
        ];
    }
}

in your grid:

'columns':
[
    //...
    [
    'attribute' => 'roleFilterInputName',
    'value' => 'role.role_name'
    ],
    //...
]

in UserSearch::search()

$query->andFilterWhere(['like', 'role.role_name', $this->roleFilterInputName])

But I guess you'll have to use 'joinWith' instead of 'with'.

Solution 2

1. Inside CGridView add below code. It will enable filter with dropDownList.

      [
           'attribute' => 'act_role_id',
           'label' => 'Actor Role',
           'value' => 'actRole.role_name',
           'filter' => yii\helpers\ArrayHelper::map(app\models\ActorRole::find()->orderBy('role_name')->asArray()->all(),'act_role_id','role_name')
       ],
   

CGridView code Snippet is as below:

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

        'userid',
        'username',
        [
            'attribute' => 'act_role_id',
            'label' => 'Actor Role',
            'value' => 'actRole.role_name',
            'filter' => yii\helpers\ArrayHelper::map(app\models\ActorRole::find()->orderBy('role_name')->asArray()->all(),'act_role_id','role_name')
        ],
        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>