CODEWITHSUNDEEP
ruby-on-railspostgresqlgreatest-n-per-group
pyfl88
pyfl88

Reputation: 1710

Select unique record with latest created date

| id    | user_id | created_at (datetime) |
| 1     | 1       | 17 May 2016 10:31:34  |
| 2     | 1       | 17 May 2016 12:41:54  |
| 3     | 2       | 18 May 2016 01:13:57  |
| 4     | 1       | 19 May 2016 07:21:24  |
| 5     | 2       | 20 May 2016 11:23:21  |
| 6     | 1       | 21 May 2016 03:41:29  |

How can I get the result of unique and latest created_at user_id record, which will be record id 5 and 6 in the above case?

What I have tried so far

So far I am trying to use group_by to return a hash like this: 

Table.all.group_by(&:user_id)

#{1 => [record 1, record 2, record 4, record 6], etc}

And select the record with maximum date from it? Thanks.

Updated solution

Thanks to Gordon answer, I am using find_by_sql to use raw sql query in ror. 

@table = Table.find_by_sql("Select distinct on (user_id) *
                            From tables
                            Order by user_id, created_at desc")

#To include eager loading with find_by_sql, we can add this

ActiveRecord::Associations::Preloader.new.preload(@table, :user)

Upvotes: 8

Views: 3872

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

In Postrgres, you can use DISTINCT ON:

SELECT DISTINCT ON (user_id) *
FROM tables
ORDER BY user_id, created_at DESC;

I am not sure how to express this in ruby.

Upvotes: 6

phil pirozhkov
phil pirozhkov

Reputation: 4909

Table
  .select('user_id, MAX(created_at) AS created_at')
  .group(:user_id)
  .order('created_at DESC')

Notice created_at is passed in as string in order call, since it's a result of aggregate function, not a column value.

Upvotes: 2

Volodymyr Balytskyy
Volodymyr Balytskyy

Reputation: 577

1) Extract unique users form the table

Table.all.uniq(:user_id)

2) Find all records of each user.

Table.all.uniq(:user_id).each {|_user_id| Table.where(user_id: _user_id)}

3) Select the latest created

Table.all.uniq(:user_id).each {|_user_id| Table.where(user_id: _user_id).order(:created_at).last.created_at}

4) Return result in form of: [[id, user_id], [id, user_id] ... ]

Table.all.uniq(:user_id).map{|_user_id| [Table.where(user_id: _user_id).order(:created_at).last.id, _user_id]}

This should return [[6,1], [2,5]]

Upvotes: 0

Related Questions