Filter And Sort A Column Fed By A Many_Many Relation

Hi all, I try to implement possibilities for user to sort and filter columns displaying field from a many_many relation. I try to follow this wiki http://www.yiiframework.com/wiki/385/displaying-sorting-and-filtering-hasmany-manymany-relations-in-cgridview/#hh9 but it only works for relation “belongs_to” that the author use though … anyway I succeeded to do it for many_many relation however I have problems with pager… Grid doesn’t display all my models although wiki…

Let’s take an example :

I have three tables : author(id,name) / author_genre(id,author_id,genre_id) / genre(id,genre_name)

relations :

in author model : "genres" => array(self::MANY_MANY, "genre", "author_genre(author_id,genre_id)");

in genre : "authors" => array(self::MANY_MANY, "author", "author_genre(genre_id,author_id)");

in author_genre : "genre" => array(self::BELONGS_TO, "genre", "genre_id");

"author" => array(self::BELONGS_TO, "author", "author_id");

I want to display in author gridview his genres and his name as follow :

[...]

'dataProvider'=>$model->search(),

[...]

'columns'=>array(

'name',

array('name'=>'hisGenre','type'=>'raw','filter'=>CHtml::listData(genre::model()->findAll(array('order'=>'name ASC')),'id','genre_name'),'value'=>'$data->getGenreForAuthor()'),

[...]

• with getGenreForAuthor() which returns a string with all author’s genre separate by a comma. I do it with a simple Chtml::listData and an implode.

• hisGenre is a public variable declared in model author, added in rules for safe and search "on".

• here is $model->search() for author:

$criteria=new CDbCriteria;

    $criteria->with=array('genres');

    $criteria->group='t.id, genres.id';

    $criteria->together=true;


    $criteria->compare('t.id',$this->id);

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

    $criteria->compare('genres.id',$this->hisGenre);


    return new CActiveDataProvider(get_class($this), array(

        'pagination'=>array(

          'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

        ),

        'criteria'=>$criteria,

        'sort'=>array('defaultOrder'=>'t.id ASC','attributes'=>array('hisGenre'=>array('asc'=>'genres.name','desc'=>'genres.name DESC'),'*')),

    ));

With that, I can filter and sort my “hisGenre” column… but the problem comes from pager… grid doesn’t display all entries…

Therefor in wiki :

but seems to not work. I use postgres btw. Another thing, I try with lazy loading but filter and sort do not work… and I tried keenLoading as well but I didn’t get it working.

Can you heelp =)

change

$criteria->group='t.id, genres.id';

to

$criteria->group='t.id';

Hi reza m, thanks for reply. If I do it I get an error SQL :

column "genres.id" must appear in the GROUP BY clause or be used in an aggregate function

I can give you the SQL statement btw

[SQL]

SELECT "t"."id" AS "t0_c0", "t"."name" AS "t0_c1", "genres"."id" AS "t1_c0", "genres"."name" AS "t1_c1" FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id") GROUP BY t.id ORDER BY t.id ASC LIMIT 10

[/SQL]

Nobody can help ? =(

Hi Zugluk,

"Group by" works differently in MySQL and in Postgres.

So you may try "distinct" instead.

What do you mean by "all entries"? Do you mean all persons or all genres?

I wrote a wiki sometime ago about searching on HAS_MANY relation. I hope you may get some hints from it.

http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation

"select => false" trick might work, I hope.

It should also apply to MANY_MANY relation with a slight modification.

And as a basic knowledge about the relational query, you may find this useful, too.

http://www.yiiframework.com/wiki/527/relational-query-lazy-loading-and-eager-loading-with-and-together/

Hi softark and thanks for reply.

When I talked about “grid doesn’t display all entries” I meant that I should have had 4 lines for example in my grid because I have 4 authors in my table. However I can see only one line with a “Displaying 1-1 of 4 results.” as title of my grid. Default order is applied on t.id asc.

Then, for example, if I click to sort by “hisGenre” column, 3 lines appears instead of 4, then I reclick on and only 1 is displayed still instead of 4… I don’t understand very well.

I tried the “select=>false” but I have same problem, plus filter doesn’t working with. I also tried $criteria->distinct but got the same problem.

I tried with HAS_MANY, HAS_ONE or BELONGS_TO relation and for them I have got no problem. So it’s specifically with MANY_MANY. But, like I said higher, filtering and sorting seem to work, it’s just that it doesn’t display all entries or results I should say, except when I use filter on “hisGenre” column. But I need to do more tests.

As far as I know, the searching on a MANY_MANY relation works as the same way as a HAS_MANY relation.

When the relation is HAS_MANY or MANY_MANY, it’s a common issue that you’ll get an incorrect numbers of main objects when you want the filtering on an attribute of related objects and the limit on the main objects at the same time.

Would you please show us what sql Yii has generated when you want to filter on "hisGenre"?

BTW, is your "hisGenre" a single value or an array of values?

These are the both requets executed when I display the grid by default :

I got "Displaying 1-1 of 4 results."

[SQL]

SELECT COUNT(DISTINCT "t"."id") FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id");

SELECT "t"."id" AS "t0_c0", "t"."name" AS "t0_c1", "genres"."id" AS "t1_c0", "genres"."name" AS "t1_c1" FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id") GROUP BY t.id, genre.id ORDER BY t.id ASC LIMIT 10;

[/SQL]

These when I sort "hisGenre" by name desc :

I got "Displaying 1-3 of 4 results."

[SQL]

SELECT COUNT(DISTINCT "t"."id") FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id");

SELECT "t"."id" AS "t0_c0", "t"."name" AS "t0_c1", "genres"."id" AS "t1_c0", "genres"."name" AS "t1_c1" FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id") GROUP BY t.id, genres.id ORDER BY genres.name DESC LIMIT 10;

[/SQL]

These when I filter "hisGenre" with a name corresponding to an id in fact. :

I got "Displaying 1-2 of 2 results."

[SQL]

SELECT COUNT(DISTINCT "t"."id") FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id") WHERE (genres.id=$1);

DETAIL: parameters: $1 = ‘15’

SELECT "t"."id" AS "t0_c0", "t"."name" AS "t0_c1", "genres"."id" AS "t1_c0", "genres"."name" AS "t1_c1" FROM "author" "t" LEFT OUTER JOIN "author_genre" "genres_genres" ON ("t"."id"="genres_genres"."author_id") LEFT OUTER JOIN "genre" "genres" ON ("genres"."id"="genres_genres"."genre_id") WHERE (genres.id=$1) GROUP BY t.id, genres.id ORDER BY genres.name DESC LIMIT 10;

DETAIL: parameters: $1 = ‘15’

[/SQL]

To display "hisGenre" I use a function where I do a Chtml::listData and an implode like :

$out = CHtml::listData($this->genres,'id','name');

return implode(", ",$out);

$this being the $data variable used in cgridview widget in admin.php file of my author model.

“LIMIT 10” in my request seems to be the problem cause there are more than 10 genres for the first author… so it doesn’t have much to display than the first author with his 10 genres…

indeed the first request gives me an array with ten lines, but each line represents a genre of the author with ID 1.

the second request gives me ten lines too. The two first lines represents 2 authors with their only unique genre (which is normal) and the last 8 represents each one genre for the same author. And as this author as more than 10 genre, we cannot see the last author which coming last with desc order.

That explain why I didn’t get any problem with HAS_MANY relation because all things I wished to display could hold in an array of 10 lines.

To watch less than all genres of the first author should have given me the clue.

then the problem seems to be what you explain in your wiki with Eager Loading (Double Query). Now I’ve to find a way to solve it with criteria

1. My wiki on HAS_MANY search relies on “group by” trick. But it won’t work with postgres.

One thing you can consider may be splitting your search function in 2 phases … Get all the possible ids of the authors without offset and limit in the first phase, and construct the data provider using the fetched ids with offset and limit in the second phase.

criteria=new CDbCriteria;

$criteria->with=array('genres');

$criteria->together=true;

$criteria->compare('t.id',$this->id);

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

$criteria->compare('genres.id',$this->hisGenre);

$criteria->select = 't.id';

$criteria->distinct = true;

$authors = Author::model()->findAll($criteria);


$authorIds = array();

foreach($authors as $author) {

    $authorsIds[] = $author->id;

}


$criteria2=new CDbCriteria;

$criteria2->with=array('genres');

$criteria2->together=false;

$criteria2->compare('t.id',$authorsIds);

return new CActiveDataProvider(get_class($this), array(

    'pagination'=>array(

       'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

    ),

    'criteria'=>$criteria2,

    'sort'=>array(

        'defaultOrder'=>'t.id ASC',

        'attributes'=>array(

              /* ... not sure if it works or not ...

              'hisGenre'=>array(

                  'asc'=>'genres.name',

                  'desc'=>'genres.name DESC'),

              */

              '*'

        )

    ),

));

2. I’m not sure if the sorting on “hisGenre” works on double query eager loading or not. You might get ‘column not found’ error.

Hi softark and thanks for your help,

I tried what you suggest. I do not have problem with the number of object displayed on grid but filtering and sorting column hisGenre does not seem to work anymore with this. Although I found a compromise with the extension of keen loading (cf. http://www.yiiframework.com/wiki/385/displaying-sorting-and-filtering-hasmany-manymany-relations-in-cgridview/#hh9) sort still does not work. Therefore I’m not far it should be enough to add genres.id in group by =( but I don’t find the way to deal it with extension cause I guess $criteria->group becomes useless with this extension.

    $criteria=new CDbCriteria;

    $criteria->with=array('genres'=>array('select'=>false));

    //$criteria->limit=-1;

    $criteria->together=true;


    $criteria->compare('t.id',$this->id);

    $criteria->compare('t.name',$this->enabled);

    $criteria->compare('genres.id',$this->hisGenre);


    return new KeenActiveDataProvider(get_class($this), array(

        'pagination'=>array(

          'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

        ),

        'withKeenLoading' => array('genres'),

        'criteria'=>$criteria,

        'sort'=>array('defaultOrder'=>'t.id ASC','attributes'=>array('assignedRole'=>array('asc'=>'genres.id','desc'=>'genres.id DESC'),'*')),

    ));

	}

does the work with 3 SQL requests using limit for filtering and default displaying, however sort doesn’t work because of “genres.id” missing in group by. $criteria->group doesn’t seem to have any effect. fffuuuuuuuu

I give up, I think sorting hisGenre column is not possible. I’m finding another way to sort it, for example sorting this column depending the number of genres that has each author. I use ‘COUNT(genres_genres.author_id) DESC’ to do it regarding the request and knowing that genres_genres is author_genre

For anyone getting trouble with postgresql, many_many relations, belongs_to relation, sorting and filtering these columns joined to these relations here is how I handle it :

$criteria=new CDbCriteria;

    $criteria->with=array('genres'=>array('select'=>false),

      'from'=>array('select'=>'name'));

    $criteria->group='t.id,from.id'

    $criteria->together=true;


    $criteria->compare('t.id',$this->id);

    $criteria->compare('t.name',$this->enabled);

    $criteria->compare('genres.id',$this->hisGenre);

    $criteria->compare('from.name',$this->isFrom);


    return new KeenActiveDataProvider(get_class($this), array(

        'pagination'=>array(

          'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

        ),

        'withKeenLoading' => array('genres'),

        'criteria'=>$criteria,

        'sort'=>array('defaultOrder'=>'t.id ASC','attributes'=>array('hisGenre'=>array('asc'=>'COUNT(genres_genres.author_id)','desc'=>'COUNT(genres_genres.author_id) DESC'),'isFrom'=>array('asc'=>'from.name', 'desc'=>'from.name desc'),'*')),

    ));

        }

with :

I have four tables : author(id,name,nationality_id) / author_genre(id,author_id,genre_id) / genre(id,genre_name) / nationality(id,name);

relations :

in author model : "genres" => array(self::MANY_MANY, "genre", "author_genre(author_id,genre_id)")

"from" => array(self::BELONGS_TO, "nationality", "nationality_id")

in genre : "authors" => array(self::MANY_MANY, "author", "author_genre(genre_id,author_id)")

in author_genre : "genre" => array(self::BELONGS_TO, "genre", "genre_id")

"author" => array(self::BELONGS_TO, "author", "author_id")

then :

comment lines giving the primary key in keenloading extension from line 195 like

            /*$pkNames = (array)$this->model->tableSchema->primaryKey;

            foreach($pkNames as $k=>$v)

            {

                $pkNames[$k] = $this->model->tableAlias.'.'.$v;

            }


            $this->criteria->group = implode(',', $pkNames);*/

keenloading extension can be downloaded here (don’t forget to load it in your main config) : https://github.com/yjeroen/ManyMany/blob/master/protected/components/KeenActiveDataProvider.php

PS : you should add only HAS_MANY or MANY_MANY relation in "withKeenLoading".

I struggled a lot to have something working well, you can have more information about keenloading here :

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