rails activerecord arel group_by something and selecting a max(col) record for each group

Question

I've got a table of visits(person, visit_type, date, ...), I would like to group by visit_type and person and then select the record in each group that has the MAX(date). I'm pretty sure it should be simple using query algebra rather than writing SQL, but can't seem to get it to work. I thought it would be something like:

Visit.group(:visit_type, :person).having("date = MAX(date)")

But that does not choose the single record from each group.

EDIT: Ahhh! after a bit more investigation - it turns out the above does work! Yay!

EDIT: Ahhh! It works on sqlite and mySql, but not on postgres, something to do with stricter implementation of the group_by function according to the standard. Boo, rubbish! This means I'm still looking for an answer.

EDIT: I do not think this gives correct results every time on sqlite.

Not sure how to mark as correct, perhaps I should just remove? Suggestions... — jjnevis, Sep 21 '12 at 16:19
It's a good question/answer, I would leave it. I learned something from it :) — iouri, Sep 21 '12 at 16:26
See edit - not working on postgres, I get the error discussed [here](http://stackoverflow.com/questions/1062158/converting-mysql-select-to-postgresql). Still looking for an elegant solution. — jjnevis, Sep 21 '12 at 17:04
You sure you don't want to use SQL? It would be pretty simple. — iouri, Sep 21 '12 at 17:09
This works but it returns an array ... Visit.group(:visit_type, :person).maximum(:date) — iouri, Sep 21 '12 at 17:34

score 1 · Accepted Answer · answered Sep 21 '12 at 17:55

Okay, here's what I'm going with for the moment:

Visit.all.group_by{|v| [v.visit_type,v.person]}.map{|key, value| value.inject{|memo, visit| memo.date > visit.date ? memo : visit}}

I know it's not pretty, but it's my daughter's 6th birthday, so I've got to go!

iouri · Answer 2 · 2012-09-21T17:45:43.350

0

Would this work?

Visit.joins('LEFT JOIN visits v2 on visits.visit_type = v2.visit_type AND visits.person = v2.person AND visits.date > v2.date')

edited Sep 21 '12 at 17:45

answered Sep 21 '12 at 16:06

iouri

2,919
1
14
11

Nope - getting duplicates, but I have a solution using ruby that works, even though it's a bit hideous. – jjnevis Sep 21 '12 at 17:52

rails activerecord arel group_by something and selecting a max(col) record for each group

2 Answers2