rails activerecord arel group_by something并为每个组选择max（col）记录

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:

我有一个访问表（person，visit_type，date，...），我想按visit_type和person分组，然后在每个组中选择具有MAX（日期）的记录。我很确定使用查询代数而不是编写SQL应该很简单，但似乎无法使其工作。我以为它会是这样的：

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.

编辑：啊！它适用于sqlite和mySql，但不适用于postgres，与根据标准更严格地实现group_by函数有关。嘘，垃圾！这意味着我仍在寻找答案。

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

编辑：我不认为每次在sqlite上给出正确的结果。

2 个解决方案

#1

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!

我知道这不漂亮，但这是我女儿的6岁生日，所以我得走了！

#2

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

#1