MySQL Order By Association Size

rubyonrails-talk
1

Is it possible to write this query (or some other query that does essentially the same) using just ActiveRecord.find?

SELECT posts.title, COUNT(comments.id) FROM posts, comments WHERE posts.id = comments.post_id GROUP BY posts.id ORDER BY 2 DESC

Basically the model Post has many Comments and I want to get the posts with most comments. Also, if possible, I'd also like the query to select posts even if the comment count is 0, with no particular order.

Thanks in advance.

2

Ok, this seems to work:

Post.find(:all, :include => [ :comments ], :order => "COUNT(comments.id) DESC", :group => "posts.id")

But it seems to return the whole thing on a single array or something, I assumed this is caused by :group, but it messes up my post listing page. Any ideas?