CGridView Page Size problem With Has_Many & Many_Many Relations

While experimenting with MANY_MANY and CGRIDVIEW, I came across to a strange one. Here is my scenario:

There are 2 Models: Games and Developers (Many-to-Many Relation)

While listing games and their developers in a gridview I use the following :

public function search()

{

$criteria=new CDbCriteria;

$criteria->with = array(‘developers’);

$criteria->together = true;

return new CActiveDataProvider(‘Game’, array(

‘criteria’=>$criteria,

‘sort’=>array(‘defaultOrder’=>‘t.name ASC’,),

‘pagination’=>array(

‘pageSize’=> 5,),));

}

If there are 5 games, each having one developer, there is no problem.

If there are 5 games, first one having 2 developers, and others have one developer, only the first 4 games are listed.

I checked the forums but couldn’t find an answer except this post which may seem related to this problem.

http://www.yiiframework.com/forum/index.php?/topic/6792-many-many-and-pagination/page__p__34681__hl__manymany#entry34681

Same happens when I try a Has_Many relation.

Also, when I add

$criteria->group = ‘t.gameid’

to my criteria, the paging problem disappears, but when I try to get games.developers, there is only the first developer from the MANY_MANY relation. Since I need all the results this is not a solution.

Thanks for your help.

Edit: When I omit $criteria->together = true; the problem again disappears, however I need it to filter the gridview by developer.name

Hi,

I have the same problem, did you find the solution?

Thank you.

David.

As you worked out, I think the ‘together’ parameter is the cause of your problems - as far as I know, ‘together’ causes the SQL to be executed in one big query, which in this case means that your first game will have two lines, plus the next three games on one line, making five lines, so your fifth game would be on the next page. You will therefore need to get rid of ‘together’ => true.

For filtering, as far as I know you can’t filter on a relation attribute, therefore you would need to create a getter and setter in your Game model, to use in your search function (see this blog for a good description), as well as possibly modify the joinType in your developer relation to INNER JOIN, so that your search only returns results for which the developer name is satisfied.

This is not, therefore, a bug.

So… has anyone thought of a workaround for this yet?

I had to deal with this issue too.

In my case I made a GROUP BY with a $criteria->join = ‘…’ and didn’t used together property at all. But in my case I have 4 tables linked and have to filter by related tables, not display data from them.

Hello again,

I think the main problem is paging goes wrong when using “together” on has-many relations. When I use “group”, it works as normal, however this time I can’t get each row from relations, just firsts.

I use “together” to reduce query count as well as to add a condition from relation table. Sure it may be more optimized to make multiple queries than one big query, I haven’t tested it. But adding a relation condition is still needed.

The blog post linked by RedRabbit is not presenting the same case as mine. It lists comments and each comment has one post title (many-to-one). So there is one query whether “together” is used or not (I tried it in a many-to-one relation grid in my project). In my case, If I use “together” there is one query (so i can add condition on relation tables), but if I don’t use “together” there are other queries for relations (so it gives an error like “unknown column”. if i add a condition from relation table).

The same issue. Why I should not use together = true if it significantly reduce query count?

No-one has anything against you using together = true to reduce your query count. But if you are using it in the same situation as the person who originally created this post, then you will never get the correct result, because whenever you have more than one child of an object, you will have more than one line in your query result for that object (if a game has two developers, then any SQL query that selects games and makes a join on developers will have two lines for that particular game).

As far as I can make out, the problem is nothing to do with Yii, but simply because the SQL query you desire does not work.

I’m happy to reconsider if you can provide the equivalent SQL that does what you want…

I apologize for my comment. I misunderstood "together" property. It does not reduce query count so significantly, only by 1 query actually.