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 group using ActiveRecord?
如何使用ActiveRecord从每个组中获取最新记录？
The solution is probably simple but I can't get my head around this.
解决方案可能很简单，但我无法理解这一点。
Thanks for any help.
谢谢你的帮助。
4 个解决方案
#1
41
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 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.
因为日期列在这里可能不是唯一的，所以我在id DESC上添加了一个额外的ORDER BY子句来打破关系，支持具有更高ID的记录，以防组中的两个记录具有相同的日期。您可能希望使用其他列，例如上次更新的日期/时间，这取决于您的用例。
Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:
接下来，遗憾的是，ActiveRecord没有用于DISTINCT ON的API，但我们仍然可以使用纯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
For other databases like MySQL this is unfortunately not as convenient. There are a variety of solutions available, see for example this answer.
对于像MySQL这样的其他数据库，遗憾的是这并不方便。有多种解决方案可供选择，例如参见本答案。
#2
0
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)
#3
-6
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的最后一个Project和组ID为2的最后一个Project。
** Update **
**更新**
Just realized you said "latest" and not "last" which required adding an order to ensure latest works. Last still pulls just one.
刚刚意识到你说“最新”而不是“最后”需要添加订单以确保最新作品。最后仍然只拉一个。
#4
-10
Project.where(:group => "1", :date => "2014-01-01").last
.last is what you are looking for.
.last就是你要找的。
#1
41
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 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.
因为日期列在这里可能不是唯一的，所以我在id DESC上添加了一个额外的ORDER BY子句来打破关系，支持具有更高ID的记录，以防组中的两个记录具有相同的日期。您可能希望使用其他列，例如上次更新的日期/时间，这取决于您的用例。
Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:
接下来，遗憾的是，ActiveRecord没有用于DISTINCT ON的API，但我们仍然可以使用纯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
For other databases like MySQL this is unfortunately not as convenient. There are a variety of solutions available, see for example this answer.
对于像MySQL这样的其他数据库，遗憾的是这并不方便。有多种解决方案可供选择，例如参见本答案。
#2
0
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)
#3
-6
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的最后一个Project和组ID为2的最后一个Project。
** Update **
**更新**
Just realized you said "latest" and not "last" which required adding an order to ensure latest works. Last still pulls just one.
刚刚意识到你说“最新”而不是“最后”需要添加订单以确保最新作品。最后仍然只拉一个。
#4
-10
Project.where(:group => "1", :date => "2014-01-01").last
.last is what you are looking for.
.last就是你要找的。