# MySQL Order By Association Size

**URL:** <https://www.ruby-forum.com/t/mysql-order-by-association-size/105284>\
**Category:** Rails\
**Created:** [May 28, 2007, 1:18pm UTC](https://www.ruby-forum.com/t/mysql-order-by-association-size/105284 "2007-05-28T13:18:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 28, 2007, 1:18pm UTC](https://www.ruby-forum.com/t/mysql-order-by-association-size/105284/1 "2007-05-28T13:18:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 28, 2007, 1:46pm UTC](https://www.ruby-forum.com/t/mysql-order-by-association-size/105284/2 "2007-05-28T13:46:12Z")

</div>

removed\_email\_address@domain.invalid 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

> **[Domain2.hu](https://domain2.hu/www.woa.hu)**
>
> www.woa.hu megvásárolható domain a kiemelt domain nevek piacterén.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 28, 2007, 2:22pm UTC](https://www.ruby-forum.com/t/mysql-order-by-association-size/105284/3 "2007-05-28T14:22:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 28, 2007, 2:13pm UTC](https://www.ruby-forum.com/t/mysql-order-by-association-size/105284/4 "2007-05-28T14:13:12Z")

</div>

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-