MySQL Order By Association Size

Rails
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

[email protected] wrote:

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.

Hi,

Check :include option for find() method:
Post.find(:all, :include => ‘comments’, :conditions => ['comments.title
= ‘foo’])
Then you can use comments.column in both :conditions and :order.

Jean-Etienne

3

I don’t see why you need to group by post, there is no need here.

4

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?

On May 28, 8:46 am, Jean-Etienne D. <rails-mailing-l…@andreas-