Ruby-on-rails 如何从ActiveRecord中的每个组中获取最新记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21289319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the latest record from each group in ActiveRecord?
提问by Tintin81
In my Ruby on Rails application I have a database structure like this:
在我的 Ruby on Rails 应用程序中，我有一个这样的数据库结构：
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...
How can I get the latest record from each groupusing ActiveRecord?
如何从每个group使用 ActiveRecord 的记录中获取最新记录？
The solution is probably simple but I can't get my head around this.
解决方案可能很简单，但我无法理解这一点。
Thanks for any help.
谢谢你的帮助。
回答by Patrick Oscity
Postgres
Postgres
In Postgres, this can be achieved with the following query.
在 Postgres 中，这可以通过以下查询来实现。
SELECT DISTINCT ON ("group") * FROM projects
ORDER BY "group", date DESC, id DESC
Because the datecolumn might not be unique here, I have added an additional ORDER BYclause on id DESCto 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.
因为这里的date列可能不是唯一的，所以我添加了一个额外的ORDER BY子句id DESC来打破关系，支持具有更高 ID 的记录，以防一组中的两个记录具有相同的日期。您可能想要使用另一列，如上次更新的日期/时间等，这取决于您的用例。
Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:
继续，不幸的是 ActiveRecord 没有 API DISTINCT ON，但我们仍然可以使用普通的 SQL select：
Project.select('DISTINCT ON ("group") *').order(:group, date: :desc, id: :desc)
or if you prefer using ARel instead of having raw SQL:
或者如果您更喜欢使用 ARel 而不是原始 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
For other databases like MySQL this is unfortunately not as convenient. There are a variety of solutions available, see for example this answer.
不幸的是，对于像 MySQL 这样的其他数据库，这并不方便。有多种解决方案可用，例如参见此答案。
回答by Navid Farjad
This works for me
这对我有用
ids = Message.select("MAX(id) AS id").group(:column_name).collect(&:id)
@result = Message.order("created_at DESC").where(:id => ids)
回答by AdamT
Something like this?
像这样的东西？
Project.select(:group).map(&:group).uniq.each do |grp|
puts Project.where(group: grp).order("date DESC").last
end
This will go through all your groups and identify the unique ones. In your example it should return ["1", "2"]. Then it iterates over that array and selects the last Project with a group id of 1 and the last Project with a group id of 2.
这将遍历您的所有组并确定唯一的组。在您的示例中，它应该返回 ["1", "2"]。然后它遍历该数组并选择组 ID 为 1 的最后一个项目和组 ID 为 2 的最后一个项目。
** Update **
** 更新 **
Just realized you said "latest" and not "last" which required adding an order to ensure latest works. Last still pulls just one.
刚刚意识到您说的是“最新”而不是“最后”，这需要添加订单以确保最新作品。最后仍然只拉一个。
回答by nzajt
Project.where(:group => "1", :date => "2014-01-01").last
.last is what you are looking for.
.last 是您要查找的内容。
