Group by and eager loading

Yii 1.1 General Discussion
1

I ran into a little problem, I dont know whether it`s a bug or normal behaviour, but any help is appreciated to achieve the same goal.

Goal?

Find a list of parties, ordered by the count of members, with all members eagerly loaded.

How I do it?

Party::model()->with(‘members’)->findAll(array(‘order’ => ‘COUNT(members.id) DESC’, ‘group’ => ‘t.id’));

What happens?

Parties are loaded with correct order, however additional query to eagerly load the members is not created and only one or zero(if there was no members for a specific party) is preloaded. This means that not only dont I get all the members, but lazy loading is not invoked either.

Thanks.

2

can you drop the created SQL here? I think the problem is the COUNT there buddy.

3 



SELECT `t`.`id` AS `t0_c0`, `t`.`founder_id` AS `t0_c1`, `t`.`president_id` AS `t0_c2`, `t`.`name` AS `t0_c3`, `t`.`country_id` AS `t0_c4`, `members`.`id` AS `t1_c0`, `members`.`username` AS `t1_c1`, `members`.`password` AS `t1_c2`, `members`.`firstname` AS `t1_c3`, `members`.`surname` AS `t1_c4`, `members`.`country_id` AS `t1_c5`, `members`.`region_id` AS `t1_c6`, `members`.`citizenship_id` AS `t1_c7`, `members`.`email` AS `t1_c8`, `members`.`party_id` AS `t1_c9` FROM `parties` `t`  LEFT OUTER JOIN `citizens` `members` ON (`members`.`party_id`=`t`.`id`)  GROUP BY t.id ORDER BY COUNT(members.id) DESC
4

Have you tried to execute that SQL statement in your SQL administrator?

It looks to me as a very weird SQL statement (never used one like that before) as you normally use GROUP BY statements when you work with aggregate functions and those are not on the ORDER BY.

Maybe you could create a statistical query to get the members like for example:




public function relations(){

  return array('memberCount'=>array(self::STAT, 'Member', 'member_id'), // I do not know how it works on your case


}

Then you can create the call by using




Party::model()->with('members','memberCount')->findAll();

And build a sort algorithm based on memberCount to render the models.

5

Sorting in php is not a very good idea, because it means bye-bye pagination - a huge performance hit. I think it is a bug, because otherwise it shouldnt bind any members to the party, but it does do that for the one thats returned with the party row.

If this is the normal behaviour, not a bug, is there any way to bind members to the party set manually?

6

Hi there juhe,

Did some research as that it seems to be a quite discussed thing and my solution is not good (creating STAT queries). Please refer to this article where zac and Mike speak about a way of doing it by using $criterias.

http://www.yiiframework.com/forum/index.php?/topic/9494-ar-statistical-query-or-manual-sql-query/page__p__46910__hl__+tat+query#entry46910

Hope it helps you.

7

This should work (declare public $mcount in model)




Party::model()->with('members')->findAll(

  array(

    'select'=>array('*', 'COUNT(members.id) as mcount'),

    'together'=>true,

    'group'=>'t.id',

    'order'=>'mcount DESC',

  )

);

/Tommy

8

This solution gives me the exact same results - only first of the members loaded.

9

have you tried with lazy loading?




$parties = Party::model()->findAll(

  array(

    'select'=>array('*', 'COUNT(members.id) as mcount'),

    'group'=>'t.id',

    'order'=>'mcount DESC',

  )

);


// then load members when needed

foreach($parties as $party)

    foreach( $party->members as $member)

....
10

Your latest snippet will just get an error, because members is not present. And the moment it is present, yii thinks that members are loaded.

11

Did you try the statistical query mentioned in #4 post by Antonio Ramirez?

12

You can`t use stats field in the order by clause. It always creates a separate query from the main one.

13

Is this problem only with SQL? Try to make the right query in your SQL editor at first. Mb COUNT(*) ? If you already created the right query show it to us.

14

You are right…

I have checked around for that type of SQL query and the queries I found, they were grouping all those fields that are not part of the aggregate function… should all be included?

15

The problem is not with the query, as you can see from my post. The problem is with the loading mechanism and internals of the AR.

16

I think I understand your problem better now. Did you come up with actual SQL code that works if pasted into a query editor? What would it be like?

Something similar to this?




SELECT p.id, m.id 

  FROM party p 

  LEFT JOIN members m ON (p.id = m.party_id) 

  ORDER BY 

    (SELECT COUNT(m2.id) 

      FROM members m2 

      WHERE m2.party_id = m.party_id)

    DESC, p.id

/Tommy

17

I saw a solution in two queries, but subquery is a neat trick. I actually got it working by setting relation together=false. Thanks for your idea tri.