Sort based on last has_many record in Rails

Question

I have a Student model and a Gpa model. Student has_many Gpa. How would I sort students based on their most recently created gpa record's value attribute?

NOTE: I don't want to sort an individual student's GPAs based on the created date. I would like to pull ALL students and sort them based on their most recent GPA record

class Student < ActiveRecord::Base
  has_many :gpas
end

@students = Student.order(...)

Answer 1

assuming the gpas timestamp is updated_at

Student.joins(:gpas).order('gpas.updated_at DESC').uniq

To include students without gpas

#references is rails 4; works in rails 3 without it
Student.includes(:gpas).order('gpas.updated_at DESC').references(:gpas).uniq

---

if you dont like the distinct that uniq creates, you can use some raw sql

Student.find_by_sql("SELECT students.* FROM students
INNER JOIN gpas ON gpas.student_id = students.id
LEFT OUTER JOIN gpas AS future ON future.student_id = gpas.student_id
  AND future.updated_at > gpas.updated_at
WHERE future.id IS NULL ORDER BY gpas.updated_at DESC")
# or some pretty raw arel
gpa_table = Gpa.arel_table
on = Arel::Nodes::On.new(
  Arel::Nodes::Equality.new(gpa_table[:student_id], Student.arel_table[:id])
)
inner_join = Arel::Nodes::InnerJoin.new(gpa_table, on)
future_gpa_table = Gpa.arel_table.alias("future")
on = Arel::Nodes::On.new(
  Arel::Nodes::Equality.new(future_gpa_table[:student_id], gpa_table[:student_id]).\
  and(future_gpa_table[:updated_at].gt(gpa_table[:updated_at])
  )
)
outer_join = Arel::Nodes::OuterJoin.new(future_gpa_table, on)
# get results
Student.joins(inner_join).joins(outer_join).where("future.id IS NULL").\
order('gpas.updated_at DESC')

Answer 2

I'm not sure that there's a way of achieving this in any kind of convenient and mostly-ruby way. The SQL required for an efficient implementation probably requires an order based on join -- something like ...

select
  ...
from
  students
order by 
  (  select gpas.value
       from gpas
      where gpas.student_id = student.id
   order by gpas.as_of_date desc
      limit 1)

I'm not sure if that's legal in MySQL, but if it is you could probably just:

Student.order("(select gpas.value from gpas where gpas.student_id = student.id order by gpas.as_of_date desc limit 1)")

On the other hand, it seems like the last value would be an important one, so you might like to implement a callback on gpas to set a "last_gpa_id" or "last_gpa_value" in the students table to make this common join more efficient.

Then of course the implementation would be trivial.

Answer 3

@students = Student.includes(:gpas).order('gpas.value DESC')

Still it's important to note that this will include Students, who has got no gpas. But you can filter that easly out with @students.delete_if{ |s| s.gpas.blank? }