如何从ActiveRecord中的每个组获取最新记录？ [英] How to get the latest record from each group in ActiveRecord?

问题描述

在我的Ruby on Rails应用程序中，我具有这样的数据库结构：

In my Ruby on Rails application I have a database structure like this:

Project.create(:group => "1", :date => "2014-01-01")
Project.create(:group => "1", :date => "2014-01-02")
Project.create(:group => "1", :date => "2014-01-03")

Project.create(:group => "2", :date => "2014-01-01")
Project.create(:group => "2", :date => "2014-01-02")
Project.create(:group => "2", :date => "2014-01-03")

# and so forth...

如何使用ActiveRecord从每个组获取最新记录？

How can I get the latest record from each group using ActiveRecord?

解决方案可能很简单，但我无法解决这个问题。

The solution is probably simple but I can't get my head around this.

感谢您的帮助。

推荐答案

Postgres

在Postgres中，可以通过以下查询来实现。

Postgres

In Postgres, this can be achieved with the following query.

SELECT DISTINCT ON ("group") * FROM projects
ORDER BY "group", date DESC, id DESC

因为 date 列可能不是在这里唯一，我在 id DESC 上添加了一个附加的 ORDER BY 子句，以打破与记录的联系较高的ID，以防组中的两个记录具有相同的日期。相反，您可能希望使用另一列，例如上次更新的日期/时间，这取决于您的用例。

Because the date column might not be unique here, I have added an additional ORDER BY clause on id DESC to break ties in favor of the record with the higher ID, in case two records in a group have the same date. You might instead want to use another column like the date/time of the last update or so, that depends on your use case.

继续前进，不幸的是，ActiveRecord没有API为 DISTINCT ON ，但我们仍然可以在 select 中使用普通SQL：

Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:

Project.select('DISTINCT ON ("group") *').order(:group, date: :desc, id: :desc)

或者如果您更喜欢使用ARel而不是原始SQL：

or if you prefer using ARel instead of having raw SQL:

p = Project.arel_table
Project.find_by_sql(
  p.project(p[Arel.star])
   .distinct_on(p[:group])
   .order(p[:group], p[:date].desc, p[:id].desc)
)

MySQL

对于像MySQL这样的其他数据库，这并不方便。有多种解决方案可用，例如参见此答案。

这篇关于如何从ActiveRecord中的每个组获取最新记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！