CODEWITHSUNDEEP
phpgridviewyii2
O Connor
O Connor

Reputation: 4392

Yii Framework 2.0 GridView and data from the join table

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.

Upvotes: 6

Views: 14766

Answers (6)

Kalpesh Desai
Kalpesh Desai

Reputation: 1421

You may write query in your UserSearch model like

if($this->role)
{
   $query->join('LEFT JOIN','role','role.user_id =   user.id')->andFilterWhere(['role.item_name' => $this->role]);
}

Upvotes: 0

srk_pathan
srk_pathan

Reputation: 396

@Ekonoval is going in right way.

Just add following in serch function of UserSearch:

After initializing ActiveDataProvider object like this:

$dataProvider = new ActiveDataProvider([
    'query' => $query,
    'pagination' => [
        'pageSize' => 5,
    ],
]);

$dataProvider->sort->attributes['roleFilterInputName'] = [
    'asc' => ['role.role_name' => SORT_ASC],
    'desc' => ['role.role_name' => SORT_DESC]
];

Upvotes: 0

Vikram
Vikram

Reputation: 45

This worked for me Table = Lead (id , year_id) Table = Year (id, text)

Added text in lead (index.php) Year::find()->all() = This code pull all value from table/ all years.

            [
            'attribute'=> 'year_id',
            'format' => 'html',
            'value' => 'year.value',
            'label' => 'Year',
            'filter' => Html::activeDropDownList($searchModel, 'year', yii\helpers\ArrayHelper::map(Year::find()->all(), 'year_id', 'value'), ['class' => 'form-control', 'prompt' => '---']),

        ],

Also it shows dropdown as well sorting.

image Showing Dropdown in Grdiview

Upvotes: 0

Yehor
Yehor

Reputation: 6793

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'.

Upvotes: 4

robsch
robsch

Reputation: 9718

Try it with

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

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

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

I just have tried it in my code, so I'm not sure if this works also with your code. But if so, I don't know why it has to be defined this way.

I guess the answer of rajesh ujade also includes this definition, however, for Yii 1.

Upvotes: 0

Rajesh Ujade
Rajesh Ujade

Reputation: 2735

  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'],
    ],
]); ?>

Upvotes: 1

Related Questions