Yii2 GridView join table column filtering/search

Yii 2.0 General Discussions
1

Hello,

I’m learning Yii2 and I’m trying to build various scenarios.

I do not understand, for GridView, if we can really create grids with any kind of SQL query, while keeping the Filtering(search)/Sort etc.

For example, the classic User + Roles tables. In this case, Yii2, User and auth_ tables.

With the following code, I managed to display a GridView without column search (filtering):





<?php


$query = (new Query())

                ->select("user.id,user.username,user.created_at,GROUP_CONCAT(auth_assignment.item_name ORDER BY auth_assignment.item_name SEPARATOR ',') as roles")

                ->from('user')

                ->join('LEFT OUTER JOIN','auth_assignment','auth_assignment.user_id = user.id')

                ->join('INNER JOIN','auth_item',' auth_item.name = auth_assignment.item_name AND auth_item.TYPE = 1')

                ->groupBy('user.id,user.username,user.created_at');                 

                

        $provider = new ActiveDataProvider([

            'query' => $query,

            'pagination' => [

                'pageSize' => 5,

            ],

            'sort' => [

                'defaultOrder' => [

                    'created_at' => SORT_DESC,

                ],

                'attributes' => ['username','roles','created_at']

            ],

            // what column is used as KEY for action buttons

            'key' => 'id'

        ]);

?> 


<?= GridView::widget([

        'dataProvider' => $users_provider,

        'columns' => [

            'id',

            'username',

            'roles',

            ['attribute' => 'created_at', 'format' => ['date', 'php:d/m/Y H:i:s']],   

            ['class'=>'yii\grid\ActionColumn'],

        ],

        'layout' => '{summary}{items}{pager}',

        'filterPosition' => FILTER_POS_FOOTER,

    	'rowOptions' => function ($model, $key, $index, $grid) {

    		},

    	'emptyText' => '-',

    	'showFooter' => true,

    ]); 

    

    ?>

As you can see, I do not use ActiveRecords for this, I haven’t actually well understood how to do it for joined tables. And personally I prefer to write my SQL queries.

How can I add the filtering functionality? If I check a generated Gii example, I see the _search, ClassSearch model etc. How should I adapt this to my approach? In my approach, I could still add the UserSearch.php model this way:





<?php




public function search($params)

    {

        $query = (new Query())

                ->select("user.id,user.username,user.created_at,GROUP_CONCAT(auth_assignment.item_name ORDER BY auth_assignment.item_name SEPARATOR '<br>') as roles")

                ->from('user')

                ->join('LEFT OUTER JOIN','auth_assignment','auth_assignment.user_id = user.id')

                ->join('INNER JOIN','auth_item',' auth_item.name = auth_assignment.item_name AND auth_item.TYPE = 1')

                ->groupBy('user.id,user.username,user.created_at');


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);


        //$this->load($params);

        

        

        $this->id = $_GET['id'];

        $this->username = $_GET['username'];

        $this->roles = $_GET['roles'];

        

/*

        if (!$this->validate()) {

            // uncomment the following line if you do not want to return any records when validation fails

            // $query->where('0=1');

            return $dataProvider;

        }

*/


        $query->andFilterWhere([

            'id' => $this->id,

        ]);


        $query->andFilterWhere(['like', 'username', $this->username])

            ->andFilterWhere(['like', 'roles', $this->roles]);


        return $dataProvider;

    }




?>

and it kind of works if I play with the url GET vars, but of course this is dummy. There is no $this->load($params) and no $this->validate(), not to mention no filter form boxes on the columns.

Thanks a lot for your help!

2

I would extend the user model like this:




class UserWithRoles extends User

{

    public $roles;

    ...

}

In the above ‘User’ is an ActiveRecord model for ‘user’ table, probably created by Gii.

The attribute ‘roles’ is added to hold the role names.

Check this section of the guide.

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#selecting-extra-fields

Then we can build the query like this:




    $query = UserWithRoles::find()

        ->select("user.id,user.username,user.created_at,GROUP_CONCAT(auth_assignment.item_name ORDER BY auth_assignment.item_name SEPARATOR '<br>') as roles")

        ->join('LEFT OUTER JOIN','auth_assignment','auth_assignment.user_id = user.id')

        ->join('INNER JOIN','auth_item',' auth_item.name = auth_assignment.item_name AND auth_item.TYPE = 1')

        ->groupBy('user.id,user.username,user.created_at');

find() will return an instance of ActiveQuery instead of Query. By using ActiveQuery, we usually don’t need to specify the table name. And by default it will return ActiveRecord objects instead of a result set array.

I believe you can sort the grid by ‘roles’ at this point.

In order to filter by ‘roles’, you can extend your search model from ‘UserWithRoles’. ‘$this->load()’ will work as expected.

3

Thank you softark. This confirms that I always have to start from an ActiveRecord model, plus to loose the saving possibility for the junction tables with the extra field solution. I was actually looking for a classic one SQL query (as complex as it can get) for which results to be delivered to a functional grid like jqGrid or JqueryDatatables. As far as I understand, it is not the case with GridView, not the same approach.

4

In Yii 2.0, the transition from ORM(ActiveRecord) to raw SQL is quite linear and smooth. There’s no steep gap between them. I’m sure you’ll like it. You can start simple and easy with ActiveRecord to get the working app very fast, and it will enable you to focus on retouching only the bottle necks with lower level approaches.

One thing you may want to note is that the related models are fetched with the separated queries in Yii’s ActiveRecord. A single SQL with many joins may not be as effective as it is usually expected.

http://www.yiiframework.com/wiki/834/relational-query-lazy-loadnig-and-eager-loading-in-yii-2-0/

5

I managed to do it. To put filters on top of a GridView starting from a non-AR model. It’s just about finding the way to do it, I suppose. I will just summarise for an Articles example:

  • seems to be working for any kind of SELECT query, I mean to show the results in a sortable filterable GridView

  • first, the "models\Article.php" class, to extend Model

  • add the columns (query fields) you want to filter on as properties of this class

  • now the "models\ArticleSearch.php" class, extends Article

  • here, the rules for the filterable columns

  • and the search() method: here we put the query and the data provider definitions, as standard; BUT instead of $query=Article::find(), as we do not use ActiveRecords model, here we use our query ((new Query()) ->select(“id, title”) ->from(‘tb_articles’)); for the filterable columns/fields, we need the $query->andFilterWhere(…) later on, as standard

  • in "controllers\ArticleController.php", for the listing method (actionIndex()), keep the usual, with $searchModel and $dataProvider

  • in the listing view, for the GridView widget, make sure you have ‘dataProvider’ => $dataProvider,‘filterModel’ => $searchModel, and that’s pretty much it