How to display, sort, and filter related model data in GridView

By | December 22, 2013

I thought I’d share how to process related model data in GridView. Starting off, we actually have two cases to consider. Let’s use the following three tables to demonstrate:

tbl_user:    id, role_id, email, username, etc
tbl_role:    id, name
tbl_profile: id, user_id, full_name

From these three tables, you can see that the user belongs to a role and has a profile.

Depending on the belongs to/has relationship, we’ll have to do slightly different things. For this example, we’ll work on getting tbl_role.name and the tbl_profile.full_name into the grid.

Let’s start with the easy part.

1) Display the data in the view file

<?php echo \yii\grid\GridView::widget([               
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        // other columns
        [
            'attribute' => 'full_name',
            'label' => 'Full Name',
            'value' => function($model, $index, $dataColumn) {
                return $model->profile->full_name;
            }
        ],
        [
            'attribute' => 'role_id',
            'label' => 'Role',
            'filter' => Role::dropdown(),
            'value' => function($model, $index, $dataColumn) {
                // more optimized than $model->role->name;
                $roleDropdown = Role::dropdown();
                return $roleDropdown[$model->role_id];
            },
        ],
        // other columns
        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>
public static function dropdown() {
    $models = static::find()->all();
    foreach ($models as $model) {
        $dropdown[$model->id] = $model->name;
    }
    return $dropdown;
}

Awesome! Believe it or not, but that’s all we need for the role info. Done and done.

Next, let’s work on the profile info. We need to update the search model ($searchModel from above).

2) Add the attribute into the model

a) public $full_name;
b) public function rules() { // add a rule so that it can validate }
c) public function attributeLabels() { // add a label to it }

3) Modify the search query to handle the new attribute

public function search($params) {

    // add the inner join
    $query = User::find();
    $query->innerJoin("tbl_profile", "tbl_user.id=tbl_profile.user_id");
    $query->with("profile"); // eager load to reduce number of queries
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    // add extra sort attributes
    $addSortAttributes = ["full_name"];
    foreach ($addSortAttributes as $addSortAttribute) {
        $dataProvider->sort->attributes[$addSortAttribute] = [
            'asc' => [$addSortAttribute => SORT_ASC],
            'desc' => [$addSortAttribute => SORT_DESC],
            'label' => $this->getAttributeLabel($addSortAttribute),
        ];
    }

    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

    // add the full_name condition at the end
    $this->addCondition($query, 'id');
    ...
    $this->addCondition($query, 'full_name', true);
}
protected function addCondition($query, $attribute, $partialMatch = false) {
    $value = $this->$attribute;
    if (trim($value) === '') {
        return;
    }

    // add table name to id to prevent ambiguous error with profile.id, 
    // i.e., "tbl_user.id"
    if ($attribute == "id") {
        $attribute = "tbl_user.id";
    }

    if ($partialMatch) {
        $query->andWhere(['like', $attribute, $value]);
    } else {
        $query->andWhere([$attribute => $value]);
    }
}

Boom! We now have a full grid with sorting and filtering on our related models.

But wait, what if we have lots of roles? What do we do if the dropdown list is too massive and we need to be able to search for the name?

Ok, then we’ll need to convert it to a text search. That means that we are no longer working with a role_id, but instead with role_name. So let’s go ahead and change all instances of it in our code.

4) Update the data in the view file

[
    'attribute' => 'role_name',
    'label' => 'Role',
    // remove dropdown filter so it reverts to text
    //'filter' => Role::dropdown(),
    'value' => function($model, $index, $dataColumn) {
        // more optimized than $model->role->name;
        $roleDropdown = Role::dropdown();
        return $roleDropdown[$model->role_id];
    },
],

Then we need to go back to the search model.

5) Update the attribute in the model

a) public $role_name; // $role_id to $role_name
b) public function rules() { // 'role_id' to 'role_name', int to string }
c) public function attributeLabels() { // 'role_id' to 'role_name' }

6) Modify the search query to handle the new attribute

public function search($params) {

    // add BOTH inner joins
    $query = User::find();
    $query->innerJoin("tbl_profile", "tbl_user.id=tbl_profile.user_id");
    $query->innerJoin("tbl_role", "tbl_user.role_id=tbl_role.id");
    $query->with("profile"); // eager load to reduce number of queries
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    // update sort for role_id -> role_name
    $dataProvider->sort->attributes["role_name"]["asc"] = ["tbl_role.name" => SORT_ASC];
    $dataProvider->sort->attributes["role_name"]["desc"] = ["tbl_role.name" => SORT_DESC];
    $dataProvider->sort->attributes["role_name"]["label"] = "Role";

    // add extra sort attributes
    $addSortAttributes = ["full_name"];
    foreach ($addSortAttributes as $addSortAttribute) {
        $dataProvider->sort->attributes[$addSortAttribute] = [
            'asc' => [$addSortAttribute => SORT_ASC],
            'desc' => [$addSortAttribute => SORT_DESC],
            'label' => $this->getAttributeLabel($addSortAttribute),
        ];
    }

    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

    // modify the addCondition, role_id -> role_name with partial match
    $this->addCondition($query, 'id');
    ...
    //$this->addCondition($query, 'role_id'); // replace with "role_name"
    $this->addCondition($query, 'role_name', true);
    ...
}

7) Modify addCondition function to convert role_name into its proper sql

protected function addCondition($query, $attribute, $partialMatch = false) {
    $value = $this->$attribute;
    if (trim($value) === '') {
        return;
    }

    // add table name to id to prevent ambiguous error with profile.id,
    // i.e., "tbl_user.id"
    if ($attribute == "id") {
        $attribute = "tbl_user.id";
    }
    // convert "role_name" to "tbl_role.name"
    elseif ($attribute == "role_name") {
        $attribute = "tbl_role.name";
    }
    
    if ($partialMatch) {
        $query->andWhere(['like', $attribute, $value]);
    } else {
        $query->andWhere([$attribute => $value]);
    }
}

And there we go – we now have a searchable text box for roles.

edited 2014/1/2 – added syntax highlighting

Leave a Reply

Your email address will not be published. Required fields are marked *