DB query that orders a collection by an association's attribute value

Question

I'd like to apply an :order filter to a db query based on an associated object's attribute.

class Report < ActiveRecord::Base
  has_many :keywords 
end

class Keyword < ActiveRecord::Base
  has_one :score
  belongs_to :report 
end

class Score < ActiveRecord::Base
  belongs_to :keyword 
end

Here's my query to pull a report's keywords, ordered by the keyword's associated score value attribute.

@keywords = @report.keywords.all(:joins => :score, :order => "scores.value DESC")

It's not working. It's simply returning the collection of keywords in no specific order.

Answer 1

That's because your query is doing two different things. It is fetching all keywords from one report based on the report ID (@report.keywords). Then You are trying to make a join with keywords and scores.
Think about SQL, the thing you want is a join from report to the two other tables.

SELECT * FROM reports 
JOIN keywords ON reports.id = keywords.id 
JOIN scores ON keywords.id = scores.id

To translate this to rails, if you are using an updated version of it, you can do something like this:

@report.keywords.includes(:score).order("scores.value DESC")

Older version of rails is quite similar, i don't know how to do it without searching.