ActiveRecord returns the newest record for each user (unique)

Question

I have a user model and a map model. The user has many cards, so the card has an attribute user_id.

I want to get the newest single Card for each user. I was 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 gives me half way, and I could, of course, iterate over these lines and grab the first one for each user. But it smells really bad to me, since I do a lot with 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 return user_ids and created_at timestamps. I cannot select any other columns (including the identifier), so what I get is useless. I also don't understand why PG won't let me select more columns than above without putting them in group_by or aggregate function.

I would rather find a way to get what I want using only ActiveRecord. I also want to write this query in raw SQL, but if I cannot do it with AR. BTW, I am using DB Postgres, which limits some of my options.

Thanks guys.

Answer 1

, , , :

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

AR: Relation, Ruby Array, Array # uniq Proc. #uniq , , , uniq, .

, , - SQL @Gene .

Answer 2

,

a) first.id != second.id
) 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')