Difference between #5 and #6 of
Displaying, sorting and filtering HasMany & ManyMany relations in CGridView

Revision #6 has been created by yJeroen yJeroen on Sep 10, 2012, 6:14:06 PM with the memo:

This tutorial gives you multiple options of how to fully implement showing, sorting and filtering related data in gridviews.
« previous (#5)

Changes

Title unchanged

Displaying, sorting and filtering HasMany & ManyMany relations in CGridView

Category unchanged

Tutorials

Yii version unchanged

Tags unchanged

CGridView, sorting, filtering, has_many, many_many, gridview

Content changed

[...]
---------

### 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.  
 
 
An example:  
 
 
```php 
$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._
 
 
### 2.4 UseCase Four - KeenLoading
 
 
**Files/Methods**  
 
- [components/KeenActiveDataProvider](https://github.com/yjeroen/ManyMany/blob/master/protected/components/KeenActiveDataProvider.php)  
 
- models/Song::search()  
 
- controllers/SongController::actionSongs()  
 
- controllers/SongController::setSearchInputs()  
 
- views/song/songsGrid  
 
- views/song/_songsGrid[models/Review::searchOne()](https://github.com/yjeroen/ManyMany/blob/master/protected/models/Review.php#L57)  
 
- [controllers/ReviewController::actionCaseOne()](https://github.com/yjeroen/ManyMany/blob/master/protected/controllers/ReviewController.php#L10)  
 
- [views/review/caseOne](https://github.com/yjeroen/ManyMany/blob/master/protected/views/review/caseOne.php#L1)  
 
- [views/review/_caseOneGrid](https://github.com/yjeroen/ManyMany/blob/master/protected/views/review/_caseOneGrid.php#L1)  
 
 
**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()](https://github.com/yjeroen/ManyMany/blob/master/protected/models/Review.php#L130)  
 
- [controllers/ReviewController::actionCaseTwo()](https://github.com/yjeroen/ManyMany/blob/master/protected/controllers/ReviewController.php#L54)  
 
- [views/review/caseTwo](https://github.com/yjeroen/ManyMany/blob/master/protected/views/review/caseTwo.php#L1)  
 
- [views/review/_caseTwoGrid](https://github.com/yjeroen/ManyMany/blob/master/protected/views/review/_caseTwoGrid.php#L1)  
 
 
**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:  
 
 
```php 
$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](https://github.com/yjeroen/ManyMany/blob/master/protected/extensions/classMap/CActiveFinder.php#L2)  
 
- [/index.php](https://github.com/yjeroen/ManyMany/blob/master/index.php#L29)  
 
- [models/Review::searchThree()](https://github.com/yjeroen/ManyMany/blob/master/protected/models/Review.php#L193)  
 
- [controllers/ReviewController::actionCaseThree()](https://github.com/yjeroen/ManyMany/blob/master/protected/controllers/ReviewController.php#L91)  
 
- [views/review/caseThree](https://github.com/yjeroen/ManyMany/blob/master/protected/views/review/caseThree.php#L1)  
 
- [views/review/_caseThreeGrid](https://github.com/yjeroen/ManyMany/blob/master/protected/views/review/_caseThreeGrid.php#L1)  
 
 
**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](https://github.com/yjeroen/ManyMany/blob/master/protected/components/KeenActiveDataProvider.php)  
 
- [models/Song::search()](https://github.com/yjeroen/ManyMany/blob/master/protected/models/Song.php#L81)  
 
- [controllers/SongController::actionSongs()](https://github.com/yjeroen/ManyMany/blob/master/protected/controllers/SongController.php#L50)  
 
- [controllers/SongController::setSearchInputs()](https://github.com/yjeroen/ManyMany/blob/master/protected/controllers/SongController.php#L7)  
 
- [views/song/songsGrid](https://github.com/yjeroen/ManyMany/blob/master/protected/views/song/songsGrid.php#L1)  
 
- [views/song/_songsGrid](https://github.com/yjeroen/ManyMany/blob/master/protected/views/song/_songsGrid.php#L1)

**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.
[...]
17 0
41 followers
Viewed: 76 696 times
Version: 1.1
Category: Tutorials
Tags: CGridView, filtering, gridview, has_many, many_many, sorting
Written by: yJeroen yJeroen
Last updated by: yJeroen yJeroen
Created on: Sep 9, 2012
Last updated: 13 years ago
Update Article
Revert to #5 | Revert to #6

Revisions

View all history