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.
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
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
}
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?
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.
$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)
....
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.
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?
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