Sort a Rails database table by column in a related model

Question

I am trying to implement the Ryan Bates sortable table column code (Railscast # 228), but I would like to be able to sort the corresponding 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 connection between the project model and the user model is notified by the foreign key "program_manager_id", which the user sets in new / editable views using the drop-down list of the collection selection. Here's the annotation part at the top of project.rb:

 # Table name: projects # program_manager_id :integer 

I want to be able to sort the list of projects in the index view using the name of the program manager, that is, by project.program_manager.name.

Ideally, I could indicate: somehow call this name, maybe something like this in the index method of my ProjectController:

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

But this clearly won't work (not to mention the fact that the Ryan routine implements this with a specific reference to the table names from the model for sorting.)

I came across some intimidating approaches that use named_scope, for example:

 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 experience, this is pretty impenetrable to me.

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

Thank you very much,

Dean

Answer 1

Let me resolve this named scope to which you referenced above. Imagine a Question model in which there are 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 

scope name. You refer like this: Question.find (: all) .most_active

 :select => "questions.*" 

by default, scopes selects all the columns from your table anyway, so this limits the results to just the question table and not the comment table. It's not obligatory.

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

This speaks for every question, I also want to get all the comments related to them. There is a column "question_id" in the comments table, which we will use to match them with the corresponding question entry. It is important. This allows us to access fields that are not relevant to our model!

  :group => "questions.id" 

This is necessary for the count () function in the order clause to tell us that we want the number of comments based on the question. We do not need the count function in our order clause, so we also do not need this group instruction

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

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

So, for our example, discarding what we don’t need and applying it to your needs, we get:

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

This named_scope will be called like this:

 Project.find(:all).by_program_manager_name 

Note that this is basically equivalent to:

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

But, as stated above, you should really know basic SQL. Your abilities will be very difficult without this understanding.