CODEWITHSUNDEEP
mysqlruby-on-railsruby
Kyle Decot
Kyle Decot

Reputation: 20835

Sort based on last has_many record in Rails

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(...)

Upvotes: 15

Views: 2691

Answers (10)

Dan Grahn
Dan Grahn

Reputation: 9424

Create a last_gpa method to get the last GPA created and then perform a standard Ruby sort.

def last_gpa
  a.gpas.order(:order=>"created_at DESC").first
end


Student.all.sort { |a, b| a.last_gpa <=> b.last_gpa }

Upvotes: -2

gwc
gwc

Reputation: 1293

You'll have to adjust the table names and column names to match your DB.

SELECT s.*, g.*
  FROM (SELECT studentId, MAX(gpaDate) as gpaDate FROM gpas GROUP BY studentId) maxgpa
  JOIN gpas g
    ON g.studentid = maxgpa.studentId
   AND g.gpaDate   = maxgpa.gpaDate
  JOIN students s
    ON s.studentId = g.studentId
 ORDER g.gpaDate DESC

Upvotes: 0

Yuri Golobokov
Yuri Golobokov

Reputation: 1975

Quick and dirty:

You can somehow implement a query like this to fetch AR objects you need:

select s.* from students s, gpas g 
  where s.id = gpas.student_id 
  and gpas.id in (select max(id) from gpas group by student_id)
  order by gpas.value

Assuming that id is higher for records with higher created_at.

OR

Nicer way:

I assume, you'll need student's last GPA score very often. Why not add a new column :last_gpa_score to Student model?

You can use callback to keep this field consistent and autofilled, i. e.:

class Gpa < ActiveRecord::Base
  belongs_to :student
  after_save :update_student_last_score

  private
    def update_student_last_score
      self.student.update_last_gpa_score
    end
end

class Student < ActiveRecord::Base
  has_many :gpas

  def update_last_gpa_score
    self.last_gpa_score = self.gpas.order("created_at DESC").first.value
  end
end

Then you can do whatever you like with last_gpa_score field on student.

Upvotes: 0

hedgesky
hedgesky

Reputation: 3311

I think, you could try this method:

class Student < ActiveRecord::Base
  attr_accessible :name
  has_many :gpas

  def self.by_last_gpas
    sql = <<-SQL
      select students.*,
        (
          select gpas.created_at from gpas where student_id=students.id
          order by gpas.created_at desc
          limit 1
        ) as last_created_at
      from students
      order by last_created_at desc
    SQL
    Student.find_by_sql(sql)
  end
end

Upvotes: 0

Mahesh
Mahesh

Reputation: 6446

This should work for you. Try this SQL query

SELECT * FROM students WHERE id IN 
        (SELECT student_id 
            FROM gpa 
            GROUP BY student_id 
            ORDER BY created_at DESC);

Upvotes: 0

firien
firien

Reputation: 1568

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')

Upvotes: 13

Jeremiah
Jeremiah

Reputation: 833

You could try adding some options to the relationships in your models.

Something like:

class Student < ActiveRecord::Base
  has_many :gpas, order: "value DESC", conditions: "foo = bar" #<-whatever conditions you want here
end

class Gpa < ActiveRecord::Base
  belongs_to :student 
end

Using options, all you have to do make a call and let Rails do most of the heavy lifting.

If you get stumped, there are several more options here: http://guides.rubyonrails.org/association_basics.html

Just do a keyword search on the page for "The has_and_belongs_to_many association supports these options:"

Upvotes: 0

p1100i
p1100i

Reputation: 3760

@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? }

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52396

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.

Upvotes: 4

Elad Meidar
Elad Meidar

Reputation: 814

Probably something like

Student.joins(:gpas).order("gpas.value DESC")

Upvotes: 0

Related Questions