CODEWITHSUNDEEP
ruby-on-railssortingnamed-scope
Segfault
Segfault

Reputation: 8290

Rails: How do you sort a model by a column in a tabel two associations away?

The problem I'm having is like this: The model to sort is SchoolClass which has_many Students which in turn has_many Projects and each project has an end_date. I need to sort the SchoolClasses four ways: First by the earliest project end_date sort ascending and descending, and second by the latest project end_date sort ascending and descending. Does this make sense?

class SchoolClass < ActiveRecord::Base
  has_many :students
end

class Student < ActiveRecord::Base
  has_many :projects
  belongs_to :school_class
end

class Project < ActiveRecord::Base
  belongs_to :student
end

The only way I can think of doing it is very brute force and involves having a methods in the SchoolClass model that return the earliest and latest project dates for that instance like so:

students.collect(&:projects).flatten.select(&:end_date).sort.last

to find the latest project end_date for that class and then fetching out all the classes of the database and sorting them by that method. Surely this is just awful though, right? I would really like to find the rails way to get this ordering (with scopes maybe?). I thought something like SchoolClasses.joins(:students).joins(:projects).order('projects.end_date ASC') might work but that will crash rails (and looking at it now the logic is wrong anyway i think).

Any suggestions?

Upvotes: 0

Views: 137

Answers (1)

Harish Shetty
Harish Shetty

Reputation: 64363

Try this:

scs = SchoolClass.joins({:students => :projects}).
  select("school_classes.id, 
          MIN(projects.end_date) AS earliest_end_date,
          MAX(projects.end_date) AS latest_end_date").
  group("school_classes.id").
  order("earliest_end_date ASC")

The objects in the scs array has following attributes:

  • id
  • earliest_end_date
  • latest_end_date

If you need additional attributes you can do the following

1) Add the additional attributes to the group and select methods

2) Query the full SchoolClass object using the id

3) Rewrite the query to use a nested JOIN

scs = SchoolClass.joins(
"JOIN (
   SELECT a.id, 
          MIN(c.end_date) AS earliest_end_date, 
          MAX(c.end_date) AS latest_end_date
    FROM  school_classes a
    JOIN  students b ON b.class_id = a.id
    JOIN  projects c ON c.student_id = b.id
    GROUP BY a.id
 ) d ON d.id = school_classes.id
").select("school_classes.*, 
           d.earliest_end_date AS earliest_end_date, 
           d.latest_end_date AS latest_end_date").
  order("earliest_end_date ASC")

Upvotes: 3

Related Questions