How can I sort by the average of a polymorphic child's attribute with ActiveRecord 2.3?

Question

I have this schema:

class Comment
  has_many :ratings, :as => :target
end

class Rating
  belongs_to :target, :polymorphic => true
end

I want to write a named scope that will sort comments by their average rating, without fetching the whole list of comments and then fetching all their ratings.

I think I need to use :include and :group, but do I also use :order? Thanks.

Answer 1

Here's what I went with:

module Ratable
  def self.included base
    base.has_many :ratings, :as => :target, :dependent => :destroy
    base.named_scope :rated, :joins => :ratings, :group => 'target_id', :select => "#{base.table_name}.*, avg(ratings.opinion) as 'average_rating'", :order => 'average_rating desc'
  end
end