Select a unique entry with the last created date.

Question

| 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 the unique and last created_at user_id entry that will record ids 5 and 6 in the above case?

What have i tried so far

So far, I'm 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 the maximum date from it? Thanks.

Updated Solution

Thanks to Gordon's answer, I am using find_by_sql to use the 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) 

Answer 1

In Postggres, you can use DISTINCT ON :

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

I'm not sure how to express it in ruby.

Answer 2

1) Extract unique users from the table

 Table.all.uniq(:user_id) 

2) Find all entries of each user.

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

3) Select the last created

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

4) The result of the return in the form: [[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]]

Answer 3

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

Note created_at is passed as a string in the order call, as this is the result of an aggregate function, not a column value.

Answer 4

If you want the latest entry:

 Table.order(:created_at).last 

If you need the last 2 entries:

 Table.order(:created_at).last(2)