Yii Framework 2.0 GridView and data from the connection table

Question

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

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

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

Everything works fine with the request.

By default, Gii does not include data from the joined 'role' table in the GridView :: widget inside the views / user / index.php page. With a join request, I was able to get data from both tables. On the page views / user / index.php, I introduced 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 role_name included in the GridView :: widget. But the problem is that there is no search field for role_name. The GridView :: widget creates a search box only for user properties. Is there a way to add a search box for the properties of the related table role, because I would also like to search through the "role name", as well as through other properties of the user model.

Answer 1

Try as follows:

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 think you will have to use 'joinWith' instead of 'with'.

Answer 2

• Inside the CGridView, add the code below. It will enable the 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 The following is a snippet:

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

Answer 3

Try using

 <?= 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 tried this in my code, so I'm not sure if this also works with your code. But if this is so, I do not know why it needs to be defined in this way.

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

Answer 4

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

Added text to lead (index.php) Year :: find () → all () = This code pulls the entire value from the 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' => '---']), ], 

It also shows a dropdown menu as well as sorting.

image Displaying a drop-down list in Grdiview

Answer 5

@Ekonoval is going in the right direction.

Just add the following Inetch function to UserSearch:

After initializing the ActiveDataProvider object, for example:

 $dataProvider = new ActiveDataProvider([ 'query' => $query, 'pagination' => [ 'pageSize' => 5, ], ]); $dataProvider->sort->attributes['roleFilterInputName'] = [ 'asc' => ['role.role_name' => SORT_ASC], 'desc' => ['role.role_name' => SORT_DESC] ]; 

Answer 6

You can write a query in your UserSearch model, for example

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