Sorting ActiveRecord models by sub-model attributes?

Question

Lets assume I have a model Category which has_many Items. Now, I'd like to present a table of Categories sorted on various attributes of Items. For example, have the category with the highest priced item at the top. Or sort the categories based on their best rated item. Or sort the categories based on the most recent item (i.e., the category with the most recent item would be first).

class Category < ActiveRecord::Base
  has_many :items

  # attributes: name
end

class Item < ActiveRecord::Base
  belongs_to :category

  # attributes: price, rating, date, 
end

Which is the best approach?

1. Maintain additional columns on the Category model to hold the attributes for sorting (i.e., the highest item price or the best rating of an item in that category). I've done this before but it's kinda ugly and requires updating the category model each time an Item changes
2. Some magic SQL incantation in the order clause?
3. Something else?

The best I can come up with is this SQL, for producing a list of Category sorted by the max price of the contained Items.

select categories.name, max(items.price) from categories join items group by categories.name

Not sure how this translates into Rails code though. This SQL also doesn't work if I wanted the Categories sorted by the price of the most recent item. I'm really trying to keep this in the database for obvious performance reasons.

Answer 1

Isn't that exactly what :joins is for?

Category.find(:all, :joins => :items, :order => 'price')

Category.find(:all, :joins => :items, :order => 'rating')

Answer 2

Assuming the attributes listed in the items model are database columns there are many things you could do.

The easiest is probably named_scopes

/app/models/category.rb

class Category < ActiveRecord::Base
  has_many :items

  # attributes: name
  named_scope :sorted_by_price, :joins => :items, :group => 'users.id', :order => "items.price DESC"    
  named_scope :sorted_by_rating, :joins => :items, :group => 'users.id', :order => "items.rating DESC"

  named_scope :active, :condition => {:active => true}


end

Then you could just use Category.sorted_by_price to return a list of categories sorted by price, highest to lowest. The advantages of named_scopes lets you chain multiple similar queries. Using the code above, if your Category had a boolean value named active. You could use Category.active.sorted_by_price to get a list of active categories ordered by their most expensive item.