如何从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 从每个 group 获取最新记录?

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 没有用于 DISTINCT ON 的 API，但我们仍然可以使用带有 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屋！