# Ordering by a child model count

**URL:** <https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276>\
**Category:** Rails\
**Created:** [November 26, 2007, 12:23pm UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276 "2007-11-26T12:23:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dougal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e495f1/32.png) [@dougal](https://www.ruby-forum.com/u/dougal)\
**Post date:** [November 26, 2007, 12:23pm UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276/1 "2007-11-26T12:23:10Z")

</div>

Hi folks.

I’m trying to order a find result by a count of the child models.

Example:

Farmer has many chickens.  
Chickens belong to farmer.

I want farmers returned, ordered by how many chickens they have.

How can I do this?

---

<div class="post-metadata">

**Author:** ![dougal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e495f1/32.png) [@dougal](https://www.ruby-forum.com/u/dougal)\
**Post date:** [November 26, 2007, 1:01pm UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276/2 "2007-11-26T13:01:45Z")

</div>

On 26 Nov 2007, at 11:23, Douglas S. wrote:

> I want farmers returned, ordered by how many chickens they have.  
> If you use a counter cache then this happens for free, if not  
> something like  
> Farmer.find\_by\_sql \<\<\_SQL  
> select farmers._, count(_) as chicken\_count from farmers  
> left outer join chichens on farmer\_id = farmers.id  
> group by farmers.id  
> order by chicken\_count desc  
> SQL

would probably do the job

Fred

---

<div class="post-metadata">

**Author:** ![dougal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e495f1/32.png) [@dougal](https://www.ruby-forum.com/u/dougal)\
**Post date:** [November 26, 2007, 1:28pm UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276/3 "2007-11-26T13:28:41Z")

</div>

You can do something like this…

#In the model…  
class Farmer \< ActiveRecord::Base  
has\_many :chicken

def self.getFarmers\_on\_chicken\_count()  
find (:all, :joins =\> “INNER JOIN chickens on chickens.farmer\_id  
= farmers.id”, :select =\> “farmers.\*, count(chickens.id)  
chickens\_count”, :group =\> “chickens.farmer\_id HAVING chickens\_count \<  
10”)  
end

end

# In the controller

@all\_needed\_farmers = Farmer.getFarmers\_on\_chicken\_count()

Here, the output will be all the farmers with chickens less than 10.

If you want to pass the parameter “count\_limit” instead of 10…

you can override the function as:

@all\_needed\_farmers = Farmer.getFarmers\_on\_chicken\_count(count\_limit)

Regards.

On Nov 26, 4:23 pm, Douglas S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![dougal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e495f1/32.png) [@dougal](https://www.ruby-forum.com/u/dougal)\
**Post date:** [November 26, 2007, 11:50pm UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276/4 "2007-11-26T23:50:26Z")

</div>

Hi again.

Kiran Soumya wrote:

> You can do something like this…
> 
> #In the model…  
> class Farmer \< ActiveRecord::Base  
> has\_many :chicken
> 
> def self.getFarmers\_on\_chicken\_count()  
> find (:all, :joins =\> “INNER JOIN chickens on chickens.farmer\_id  
> = farmers.id”, :select =\> “farmers.\*, count(chickens.id)  
> chickens\_count”, :group =\> “chickens.farmer\_id HAVING chickens\_count \<  
> 10”)  
> end
> 
> end

Thanks for the replies.

This was along the lines of what I was looking for, but I forgot the  
most important thing…

Say chickens have a color, simply a string, such as ‘red’.

How would I order the farmers by the number of red chickens they have?

Sorry to be such a pain, I should have remembered this the first time  
round.

Thanks again.

---

<div class="post-metadata">

**Author:** ![dougal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e495f1/32.png) [@dougal](https://www.ruby-forum.com/u/dougal)\
**Post date:** [November 27, 2007, 12:07am UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276/5 "2007-11-27T00:07:08Z")

</div>

Frederick C. wrote:

> add chickens.colour = ‘red’, either as a condition or in the join  
> clause. Note that you won’t get back farmers with no chickens, which  
> may or may not be the right thing (if you do, then you should be using  
> a left outer join).
> 
> Fred

Thanks for the quick reply Fred, was thwarted by my failed attempts so  
far.

---

<div class="post-metadata">

**Author:** ![dougal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e495f1/32.png) [@dougal](https://www.ruby-forum.com/u/dougal)\
**Post date:** [November 27, 2007, 12:02am UTC](https://www.ruby-forum.com/t/ordering-by-a-child-model-count/121276/6 "2007-11-27T00:02:48Z")

</div>

On 26 Nov 2007, at 22:50, Douglas S. wrote:

> > def self.getFarmers\_on\_chicken\_count()
> 
> This was along the lines of what I was looking for, but I forgot the  
> most important thing…
> 
> Say chickens have a color, simply a string, such as ‘red’.
> 
> How would I order the farmers by the number of red chickens they have?
> 
> Sorry to be such a pain, I should have remembered this the first time  
> round.

add chickens.colour = ‘red’, either as a condition or in the join  
clause. Note that you won’t get back farmers with no chickens, which  
may or may not be the right thing (if you do, then you should be using  
a left outer join).

Fred