Yii 1.1: Displaying, sorting and filtering HasMany & ManyMany relations in CGridView

40 followers

This tutorial gives you multiple options of how to fully implement showing, sorting and filtering related data in gridviews.

Intro

The app shows four ways of how to display related data in a fully functional gridview. We (tom[] and yjeroen from the #yii chatroom) have been working on this to show fellow Yii users what the possibilities are. I would also like to thank tom[] for giving me these code challenges. In October, I will create unit tests for UseCase Four - KeenLoading and turn it into an Extension.

Github main project: https://github.com/yjeroen/ManyMany
Demo: http://yiitryout.site90.net/ManyMany/

All gridviews have working implementations of paging, sorting and filtering.

  1. UseCase One: Only primary data is loaded with the CActiveDataProvider, related data is Lazy loaded.
  2. UseCase Two: Related data is loaded using a GROUP_CONCAT query. This is the most data efficient way, but you can't do any manipulation using the join-model or related-model.
  3. UseCase Three: You normally can't use Yii's Eager Loading method in gridviews in combination with a pager. (If you don't use the pager, you can Eager load without problems.)
    The reason why it won't work with the pager is because the pager adds LIMIT and OFFSET to the query, but those are static. In combination with JOIN statements, this becomes a problem. I made some changes to CActiveFinder so the correct LIMIT and OFFSET numbers are calculated using two seperate COUNT queries. This will enable you to use Yii's Eager loading without any problems.
  4. UseCase Four - KeenLoading: This method uses a custom KeenActiveDataProvider, which loads all related data in a Keen way using a seperate query.

With these four methods, it might be hard to choose which one to use. Here are some considerations:

  1. UseCase One
    Pro: Default Yii lazy loading
    Con: A lot of queries(!)

  2. UseCase Two
    Pro: Most data efficient
    Pro: Only one query
    Con: No manipulation of data of the related Model(s)

  3. UseCase Three
    Pro: Default Yii eager loading
    Neutral: Up to two extra COUNT queries. (For a max of three queries)
    Con: Like all eager loading, this can become data inefficient

  4. KeenLoading
    Pro: Able to manipulate data of the related Model(s)
    Pro: Still very efficient
    Neutral: One extra query for loading the related Model(s)


1. CGridView: Paging, Sorting and Filtering

1.1 Paging

The CPagination object in your CActiveDataProvider adds LIMIT and OFFSET to the SQL query that Yii performs. This can become a problem when you do queries with JOIN in them(if you set together=true for eager loading), because the database returns multiple rows for one model, while Yii expects one row returned for each model.
The easiest way to fix this, is to group by the primary key(s) of your main model. The UseCases shown below all do this in one way or another.

$criteria = new CDbCriteria;
    $criteria->with = array('song');
    $criteria->group = 't.id';
    $criteria->together = true;

Reference: Review::searchOne()

1.2 Sorting

When you have a column in your CGridView that isn't an attribute of the model, Yii doesn't automatically know how to sort. But we can tell the sort parameter of the CActiveDataProvider how.
First, you have one or more columns in the View that show related data. Those columns have a 'name' attribute
(example: array('name' => 'song.album')), and we have to tell Yii how to sort that song's attribute called album.

You have to add the attribute called song.album to the attributes array of 'sort'. Then you tell Yii how to sort that attribute ascending, and descending. Like this:

return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
        'sort'=>array(
            'attributes'=>array(
                'song.album'=>array(
                    'asc'=>'song.album',
                    'desc'=>'song.album DESC',
                ),
                '*',
            ),
        ),
    ));

Reference: views/review/_caseOneGrid, Review::searchOne()

1.3 Filtering

This one will be a little bit more complex to implement. Think of the filters on the top of the CGridView as normal <INPUT> fields (because they are!), just like you would make them with CHtml::activeTextField($review, 'review'). Now of course, such a textfield wants a $model in the first parameter, and an attribute name in the second parameter.

We are going to base the filter's <INPUT> field on the related Model. The advantage of this is that you keep Yii's default functionality, like validation of the input.

First, in the Controller, we create such a model for the column with the related data: $song = new Song('search');
Then we unset its attributes, just like we do for the main model: $song->unsetAttributes();

Okay, so now we have a $song model variable that we could use in an activeTextField. We have to pass this variable to the View. We use a more elegant approach to this, and put this variable $song inside a property of the main model Review. To do this, we first have to declare this property in the Review model: public $searchSong;
Now, back to the controller, we put the Song model into that property: $review->searchSong = $song;

In the view, we create a column with a self defined filter, like this:

array(
        'name' => 'song.name',
        'filter' => CHtml::activeTextField($review->searchSong, 'name'),
    ),

As you can see, we pass the Song model into the first parameter, and an attribute of that model into the second parameter. So far, so good.. If we refresh the page it shows an <INPUT> field on top of the column and we can type in there. But what happens if we type and then press ENTER? A submit action will be performed back to the Controller.

In the Controller, we have to catch the send data and place it into the $song model. We do that in the same way as you'd do that for the main Model:

if (isset($_GET['Song'])) {
        $song->attributes = $_GET['Song'];
    }

Now we have to go to the place where CGridView's searching magic actually happens, the method (usually $model->search()) in the main Model that providers a DataProvider to the CGridView.

Here, we simply add extra $criteria->compare()'s for the column we want to filter. We use the model inside the $searchXxx property to make this work, since we added the searched value earlier in the Controller.

$criteria->compare('song.name', $this->searchSong->name, true);

Reference: ReviewController::actionCaseOne(), Review::$searchSong, views/review/_caseOneGrid, Review::searchOne()

2. UseCases

2.1 UseCase One - Lazy Loading

Files/Methods
- models/Review::searchOne()
- controllers/ReviewController::actionCaseOne()
- views/review/caseOne
- views/review/_caseOneGrid

Explanation
You group the primary keys of Review, and set together to true. You don't select any data from Genre, because then its lazy loaded for each row.
Because you're grouping the primary keys, the database returns only one row for each primary Model. This is why the pager doesn't break even though you set $criteria->together to true.

Additionally, make sure to set the relations in $criteria->with that are lazy loaded to array('select'=>false). This is more efficient since you're loading this data in a lazy way, so you don't need it in the first SELECT that the CActiveDataProvider performs..

2.2 UseCase Two - GROUP_CONCAT

Files/Methods
- models/Review::searchTwo()
- controllers/ReviewController::actionCaseTwo()
- views/review/caseTwo
- views/review/_caseTwoGrid

Explanation
Same as the explanation of UseCase One. In addition:
You set a $criteria->select, that selects a GROUP_CONCAT of the data from Genre. Don't forget to set the attributes of the main Model here or else those aren't loaded. Note that you don't have to include the primary keys in this select statement. Those are automatically added by Yii.

A slight disadvantage about this method is that you can't use the loaded related data in your app like $reviewModel->genres->name, because the data is loaded in the class property `Review::$allGenres.

An example:

$criteria->select = array(
        //This attribute (allGenres) has to be added in the Model as a public property!
        'GROUP_CONCAT(genres.name ORDER BY genres.name SEPARATOR \', \') AS allGenres', 
        't.review',
   );

Note: In addition, you can look at _caseTwoGrid.php to see how you can use a dropDownList in a gridview filter to search for a genre.

2.3 UseCase Three - Custom CActiveFinder

Files/Methods
- extensions/classMap/CActiveFinder
- /index.php
- models/Review::searchThree()
- controllers/ReviewController::actionCaseThree()
- views/review/caseThree
- views/review/_caseThreeGrid

Explanation
Using classMap you import a custom CActiveFinder that enhances the eager loading magic of Yii. Easiest, but the disadvantage is that it does another 2 COUNT queries for the pager to work. Like the normal Eager loading way of Yii, this can become data inefficient in some cases.

The extra COUNT queries will only be performed when:
1. the primary table is joined with HAS_MANY or MANY_MANY relations
2. Columns of those relations are selected
3. $criteria->group has been set
4. $criteria->together has been set to true

Note: I didn't test this custom CActiveFinder as much as the KeenLoading extension. I don't recommend using this usecase.

2.4 UseCase Four - KeenLoading

Files/Methods
- components/KeenActiveDataProvider
- models/Song::search()
- controllers/SongController::actionSongs()
- controllers/SongController::setSearchInputs()
- views/song/songsGrid
- views/song/_songsGrid

Explanation
Related data is loaded in a keen fashion. Using KeenActiveDataProvider, the related models are loaded in a separate query and then put into the relation properties of the earlier loaded models.

In your Models search function, you return a new KeenActiveDataProvider, instead of a CActiveDataProvider. The KeenActiveDataProvider has another option named 'withKeenLoading', where you can set the relations that you want to load in a second(or multiple) queries.

An example:

return new KeenActiveDataProvider($this, array(
        'criteria' => $criteria,
        'withKeenLoading' => array('hasGenres.genre'),
    ));

Note: In addition, you can look at Song::search() to see how you can enable the gridviews filter to search for multiple Genres using a comma or space in the input field.
2nd Note: You can also look at SongController::setSearchInputs() to take a look at a method that generalizes a way to set search inputs.

3. Extra: Full explanation of KeenActiveDataProvider

KeenActiveDataProvider implements a data provider based on ActiveRecord and is extended from CActiveDataProvider.

KeenActiveDataProvider provides data in terms of ActiveRecord objects. It uses the CActiveRecord::findAll method to retrieve the data from database. The criteria property can be used to specify various query options. If you add a 'with' option to the criteria, and the same relations are added to the 'withKeenLoading' option, they will be automatically set to select no columns.
ie. array('author'=>array('select'=>false)

HAS_ONE and BELONG_TO type relations shouldn't be set in withKeenLoading, but in the $criteria->with, because its more efficient to load them in the normal query.

There will be a CDbCriteria->group set automatically, that groups the model to its own primary keys.

The relation names you specify in the 'withKeenLoading' property of the configuration array will be loaded in a keen fashion. A separate database query will be done to pull the data of those specified related models.

An example:

$dataProvider=new KeenActiveDataProvider('Post', array(
        'criteria'=>array(
            'condition'=>'status=1',
            'with'=>array('author'),
        ),
        'pagination'=>array(
            'pageSize'=>20,
        ),
        'withKeenLoading'=>array(
            'author',
            'comments'=>array('condition'=>'approved=1', 'order'=>'create_time'),
        )
));

The property withKeenLoading can be set as a string with comma separated relation names, or an array. The array keys are relation names, and the array values are the corresponding query options.

In some cases, you don't want all relations to be Keenly loaded in a single query because of data efficiency. In that case, you can group relations in multiple queries using a multidimensional array. (Arrays inside an array.) Each array will be keenly loaded in a separate query.
Example:

'withKeenLoading'=>array(
        array('relationA','relationB'),
        array('relationC')
    )

Total 8 comments

#12875 report it
yJeroen at 2013/04/17 08:41am
CPagination bug?

Possibly. Depends on your perspective? I don't know the opinion of the yii team about this.

#12873 report it
marcovtwout at 2013/04/17 08:03am
Manually adding group by t.id in CGridView with with() set

1.1: http://www.yiiframework.com/wiki/385/displaying-sorting-and-filtering-hasmany-manymany-relations-in-cgridview/#hh2

Shouldn't this be considered a Yii bug?

#12044 report it
le_top at 2013/02/23 05:40am
Use of KeenActiveDataProvider with RelatedSearchBehavior

Hi If you are using the KeenActiveDataProvider,you might be interested in using it with the RelatedSearchBehavior Extension.

That extension makes it easy to use fields from related tables and make them searchable and sortable through the DataProvider for viewing in a Grid View (the main use for a data provider).

I think that there would even be some benefit in 'merging' the extensions: KeenActiveDataProvider could use the configuration done for the RelatedSearchBehavoir.

#11907 report it
yJeroen at 2013/02/12 02:32pm
through

Hi Sukhwinder,

There are some advanced uses for it. For most simple many_many uses, a MANY_MANY relationship suffices. That way, you don't need a specific model for the pivot table.

#11842 report it
Sukhwinder at 2013/02/07 02:24am
what is difference between using Many_Many relation Vs "through" with Has_Many

hi I im not able to understand, why you have used relation in model Gener.php as

'songs'  => array(self::MANY_MANY, 'Song', 'song_genre(genre_id, song_id)')

where as in Song.php as

'hasGenres' => array(self::HAS_MANY, 'SongGenre', 'song_id'),
'genres'=> array(self::HAS_MANY, 'Genre', 'genre_id', 'through'=>'hasGenres'),

why not used in Song.php like this :

'genres'=> array(self::MANY_MANY, 'Song', song_genre(song_id,gener_id)')

what is difference between these two? where should we use which? please help. Thanks

#11721 report it
rclai89 at 2013/01/29 01:25pm
This is very helpful

Will keen loading work for a MANY_MANY relation?

Edit:

Well that was a dumb question, just tried it out and it WORKED! This KeenADP class just made my day.

I was doing the GROUP_CONCAT way and my issue with that was the same issue that lmre was talking about; losing the other genres when searching.

With the KeenADP I was able to preserve the other genres when searching by one particular one.

#9791 report it
yJeroen at 2012/09/10 01:54pm
Usecase 2 - $criteria->join instead of $criteria->select

Hi Imre,

That's correct. That's by design if you look at the SQL that Yii generates.

If you really want all genres of a Review to show when you're searching for a Genre, that is possible. But it's a bit more complex. You will need to set a custom $criteria->join string that does a subquery. I've only done that for has_many relations before though, not for many_many relations.

Here is an example from an app of mine where Incident HAS_MANY Towns:

$criteria->select = array('t.id', 'towns.name AS searchTowns', /* etc */ );
$criteria->join =  "LEFT OUTER JOIN 
        (SELECT towns.incident_id, GROUP_CONCAT(towns.name ORDER BY towns.name SEPARATOR ',') as name
         FROM `incident_towns` `towns`
         LEFT OUTER JOIN `incident` `t` 
         ON (`towns`.`incident_id`=`t`.`id`)
         GROUP BY towns.incident_id) AS towns 
    ON (`towns`.`incident_id`=`t`.`id`) ";
#9779 report it
Imre at 2012/09/10 04:41am
very nice

Great tutorial.

However on cases 2 and 3 when you search for a genre other genres are not displayed anymore.

Leave a comment

Please to leave your comment.

Write new article