Ordering by a child model count

Rails
1

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?

2

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

3

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. [email protected]

4

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.

5

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.

6

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