Ordering rows by a number of referencing records


#1

Hello all,

There are 2 tables in a one-to-many relation. I want to order rows in
the parent
table according to the number of rows in the child table referencing
the parent table.

In SQL I would do it the following way:

select service_partners.id, count(couriers.first_name) as courier_count
from service_partners
left join couriers on service_partners.id =
couriers.service_partner_id
group by service_partners.id
order by courier_count;

Using ActiveRecord it is done by

ServicePartner.find(:all,
:select => 'service_partners.id, count(couriers.first_name) as cn ',
:joins => ‘left join couriers on service_partners.id =
couriers.service_partner_id’,
:group => 'service_partners.id ',
:order => ‘cn asc’

You might think why the guy is not using :include => :couriers instead
of :joins.
The answer is that using :include makes ActiveRecord ignore (and I
can understand why) the :select argument which I need.

So my question is if anybody sees a more elegant way to do this?

Cheers,
Yuri