ActiveRecord return the newest record per user (unique)

Question

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.

Answer 1

We join the cards table on itself, 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')

Answer 2

This is a bit late, but I am working on the same matter, and i found this one works for me :

Card.all.group_by(&:user_id).map{|s| s.last.last}

What do you think ?