Guys, I really need some help here. How can I findAll() and order by a count? For example, getting a list of users by the number of posts made, or tags by the number times they are used.
I have my models all set up with self::STAT relations, but I can’t figure out how to order by a count. Right now I am just doing :
User::model()->with('postsCount')->findAll();
I’d like to get it into order. Anyone??
Please, can anyone point me the right way? I’m stuck at loads of points on my app because of this!
Didn’t look very close, but one note: You can’t order by a STAT relation, as it will always use a separate query. What you want to do is to LEFT JOIN the posts and then GROUP BY user id. Can’t give you details for AR, but in the end you need a query similar to this:
SELECT u.*, count(u.id) as PostCount
FROM user u
LEFT JOIN post p ON u.id=p.user_id
GROUP BY u.id
Play around a little, you’ll surely find out how to do that with AR.
Nice one, that’s exactly what I needed - thank you. I’ve put it into my model as a scope
public function scopes()
{
return array(
'byPostCount'=>array(
'order'=>'postCount DESC',
'select'=>'t.*, count(p.userId) as postCount',
'join'=>'LEFT JOIN Post p USING (userId)',
'group'=>'t.userId',
),
);
}
// Call it with
$users = User::model()->byPostCount()->with('postCount')->findAll();
Can any SQL wizards comment on the efficiency of that?
It’s very efficient, if the columns used for JOIN condition have an index, which they usually have if you’ve defined constraints in InnoDB.
If it is a frequent operation, then a more efficient way is to create a field “postCount” in your users’ table. You’ll need to update it when a user creates a new post or some posts are deleted.
I’ve had a look at CActiveFinder and am not sure anymore wether self::STAT always uses a separate query. It looks like it also tries to do a LEFT JOIN … GROUP BY sometimes. Actually that’s one of the reasons i hardly use it: It’s a little to obscure to me 
.
Qiang, maybe you can shed some light on this?