Sorting a Rails database table by a column in an associated model

Question

I'm trying to implement Ryan Bates' sortable table columns code (Railscast #228) but I'd like to be able to sort on an associated column. In particular, I have the following models and associations:

class Project < ActiveRecord::Base
  belongs_to :program_manager, :class_name => "User"

class User < ActiveRecord::Base
  has_many :program_manager_projects, :class_name => "Project", :foreign_key => "program_manager_id"

The association between the Project model and the User model is mediated by the 'program_manager_id' foreign key, which the user sets in the new/edit views using a collection-select dropdown. Here's part of the annotation at the top of project.rb:

# Table name: projects
# program_manager_id :integer

I want to be able to sort my list of projects in the index view by the program manager's name, i.e., by project.program_manager.name.

Ideally, I'd be able to point :order to this name somehow, perhaps with something like this in the index method of my ProjectsController:

@projects = Project.find(:all, :order => project.program_manager.name)

But that obviously won't work (not to mention Ryan's routine implements this with a specific reference to table names from the model to be sorted.)

I've come across some intimidating approaches that use named_scope, such as:

named_scope :most_active, :select => "questions.*", :joins => "left join comments as comments_for_count on comments_for_count.question.id = questions.id", :group => "questions.id", :order => "count(questions.id) desc"

But given my lack of MySQL expertise, this is fairly impenetrable to me.

Can anyone help me either generalize the named_scope example above for my specific case, or point me to a more straightforward strategy?

Thanks very much,

Dean

Answer 1

Let's dissect that named scope you referenced above. Imagine a model Question which has many Comments.

named_scope :most_active, :select => "questions.*", :joins => "left join comments as comments_for_count on comments_for_count.question.id = questions.id", :group => "questions.id", :order => "count(questions.id) desc"

:most_active

the name of your scope. You would reference thusly: Question.find(:all).most_active

:select => "questions.*"

by default scopes selects all columns from your table anyway, so this limits the results to only the questions table, and not the comments table. This is optional.

:joins => "left join comments as comments_for_count on comments_for_count.question.id = questions.id"

this is saying for every question, I also want to get all comments associated with them. The comments table has a column 'question_id' which is what we'll be using to match them up to the appropriate question record. This is important. It allows us access to fields that are not on our model!

 :group => "questions.id"

This is required for the count() function in the order clause to tell us that we want the count of comments based on question. We don't need the count function in our order clause, so we also don't need this group statement

:order => "count(questions.id) desc"

Return the results in order of number of comments, highest to lowest.

So for our example, discarding what we don't need, and applying to your needs, we end up with:

:named_scope :by_program_manager_name, :joins => "left join users on projects.program_manager_id = users.id", :order => "users.name"

This named_scope would be called thusly:

Project.find(:all).by_program_manager_name

Note this is basically equivalent to:

Project.find(:all, :joins => "left join users on projects.program_manager_id = users.id", :order => "users.name")

But, as cam referenced above, you should really know the underlying SQL. Your abilities will be severely hampered without this understanding