# Ordering rows by a number of referencing records

**URL:** <https://www.ruby-forum.com/t/ordering-rows-by-a-number-of-referencing-records/104863>\
**Category:** Rails\
**Created:** [May 23, 2007, 10:53am UTC](https://www.ruby-forum.com/t/ordering-rows-by-a-number-of-referencing-records/104863 "2007-05-23T10:53:27Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![yuleven](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/c89c15/32.png) [@yuleven](https://www.ruby-forum.com/u/yuleven)\
**Post date:** [May 23, 2007, 10:53am UTC](https://www.ruby-forum.com/t/ordering-rows-by-a-number-of-referencing-records/104863/1 "2007-05-23T10:53:27Z")

</div>

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