:order by association field

rubyonrails-talk
1

Hi. I'm trying to do something that should be a fairly common pattern, but I'm currently not sure how.

BACKGROUND: model Family has_one Father. also, Family has field "country_of_origin" model Father has a number of fields, such as first_name, age, etc.

It's easy to sort based on families' fields: sorted_families = Family.find(:all, :order => 'country_of_origin')

But, how do I sort based on the fields of the child? For example, how do I sort families based on the first name of the associated "father" field? I.e.,

sorted_families = Family.find(:all, :order => 'father.first_name')

clearly this doesn't work... but you probably understand what I'm trying to do. If I can't do this directly in Rails, what's the cleanest direct SQL command that will achieve this? Perhaps subqueries/nested queries will do the trick.

As a related question, how would I sort based on the count of a child association? For example, assume an Order has_many LineItems; how do I sort orders based on the count of the associated number of line items?

Thanks in advance for your help! Mark

2

Try

sorted_familes = Family.find(:all, :include => :father, :order => 'fathers.first_name')

3

sorted_familes = Family.find(:all, :include => :father, :order => 'fathers.first_name')

Thanks! This does indeed work. Now, how would I go about doing the sorting by the count of the children?

4

my first guess would be

sorted_familes = Family.find(:all, :include => :child, :order => ‘COUNT(children.*)’)

if you tail -f the development.log, you’ll notice that the :order value is being inserted verbatim. it’s nothing but SQL syntaxt. The :include => child is simply creating a LEFT OUTER JOIN for you between the families and children tables. This is what the rails developers refer to as eager loading of associations. You can find more information under ActiveRecord::Associations::ClassMethods and the ActiveRecord#find method in the API http://api.rubyonrails.org/