ActiveRecord返回每个用户的最新记录（唯一）(ActiveRecord return the newest record per user (unique))

ActiveRecord返回每个用户的最新记录（唯一）(ActiveRecord return the newest record per user (unique))

我有一个用户模型和一个卡模型。 用户有很多卡，因此卡具有属性user_id 。

我想为每个用户获取最新的单卡。 我已经能够做到这一点：

Card.all.order(:user_id, :created_at) # => gives me all the Cards, sorted by user_id then by created_at

这让我走了一半，我当然可以遍历这些行并获取每个用户的第一行。 但这对我来说真是太糟糕了，因为我在Ruby中使用Arrays做了很多这样的事情。

我也可以这样做：

Card.select('user_id, max(created_at)').group('user_id') # => gives me user_id and created_at

...但我只返回user_ids和created_at时间戳。 我不能选择任何其他列（包括id），所以我得到的回报毫无价值。 我也不明白为什么PG不会让我选择比上面更多的列而不将它们放在group_by或聚合函数中。

我更愿意找到一种只使用ActiveRecord获得我想要的东西的方法。 我也愿意在原始SQL中编写这个查询但是如果我不能用AR完成它。 顺便说一句，我使用的是Postgres数据库，这限制了我的一些选择。

多谢你们。

I've got a User model and a Card model. User has many Cards, so card has a attribute user_id.

I want to fetch the newest single Card for each user. I've been able to do this:

Card.all.order(:user_id, :created_at) # => gives me all the Cards, sorted by user_id then by created_at

This gets me half way there, and I could certainly iterate through these rows and grab the first one per user. But this smells really bad to me as I'd be doing a lot of this using Arrays in Ruby.

I can also do this:

Card.select('user_id, max(created_at)').group('user_id') # => gives me user_id and created_at

...but I only get back user_ids and created_at timestamps. I can't select any other columns (including id) so what I'm getting back is worthless. I also don't understand why PG won't let me select more columns than above without putting them in the group_by or an aggregate function.

I'd prefer to find a way to get what I want using only ActiveRecord. I'm also willing to write this query in raw SQL but that's if I can't get it done with AR. BTW, I'm using a Postgres DB, which limits some of my options.

Thanks guys.

最满意答案

我们加入卡片表，ON

a） first.id != second.id b） first.user_id = second.user_id c） first.created_at < second.created_at

Card.joins("LEFT JOIN cards AS c ON cards.id != c.id AND c.user_id = cards.user_id AND cards.created_at < c.created_at").where('c.id IS NULL')

I've found one solution that is suboptimal performance-wise but will work for very small datasets, when time is short or it's a hobby project:

Card.all.order(:user_id, :created_at).to_a.uniq(&:user_id)

This takes the AR:Relation results, casts them into a Ruby Array, then performs a Array#uniq on the results with a Proc. After some brief testing it appears #uniq will preserve order, so as long as everything is in order before using uniq you should be good.

The feature is time sensitive so I'm going to use this for now, but I will be looking at something in raw SQL following @Gene's response and link.