Reputation: 20835
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
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
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
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
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
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
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
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
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
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
Reputation: 814
Probably something like
Student.joins(:gpas).order("gpas.value DESC")
Upvotes: 0