GridView widget: sorting and filtering by indirectly related model

Yii 2.0 Extensions
1

Hello.

I’m not sure if the title of this topic is clear enough, so let me explain myself a bit.

I have a product table for drinks. Each drink has a subType, and each subType as a type.

The types are wine, rhum, vodka and so on.

The subTypes are dependent categories. For instance:

  • Wine subTypes are red wine, white wine, pink wine.
  • Rhum subTypes are white rhum, aged rhum, golden rhum and so on.

So, in order to design a concise database and removing extra references, my product model contains only the subType attribute, because I can have access to its type by calling $model->subType->type.

Now, the tricky part comes when trying to use this indirectly related type in the GridView widget. I managed to display the type, but I can’t figue out how to filter and sort by it.

Has anyone come across this situation?

2

Its been a while since I’ve used a GridView component.
The reason why GridView is not allowing to sort relational data by default is that Yii usually fetches related data in seperate queries for increased performance. However, when you want to sort related data you have to tell Yii that you want to perform an INNER JOIN (or LEFT JOIN if you prefer) on the related data:

SiteController.php

    public function actionDrinks()
    {
        $dataProvider = new \yii\data\ActiveDataProvider([
            'query' => \app\models\Drink::find()
                ->innerJoinWith('subType')
                ->innerJoinWith('subType.type'),
            'pagination' => [
                'pageSize' => 5,
            ],
            'sort' => [
                'attributes' => [
                    'id',
                    'name',
                    'subType.name' => [
                        'asc' => ['sub_type.name' => SORT_ASC],
                        'desc' => ['sub_type.name' => SORT_DESC],
                    ],
                    'subType.type.name' => [
                        'asc' => ['type.name' => SORT_ASC],
                        'desc' => ['type.name' => SORT_DESC],
                    ],
                ],
            ],
        ]);
        return $this->render($this->action->id, [
            'dataProvider' => $dataProvider,
        ]);
    }

drinks.php:

    <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
            'id',
            'name:text:Drink Name',
            'subType.name:text:Sub Type Name',
            'subType.type.name:text:Type Name',
            'created_at:datetime',
        ],
    ]) ?>

As for filtering, you can stick to the guide: https://www.yiiframework.com/doc/guide/2.0/en/output-data-widgets#filtering-data

// Edit: I just saw that there is also a wiki article about your use case: https://www.yiiframework.com/wiki/653/displaying-sorting-and-filtering-model-relations-on-a-gridview

3 Likes