CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
Misha Moroshko
Misha Moroshko

Reputation: 171341

How to sort users by the most recent post?

I have two models: User, and Post which has user_id column (foreign key), and created_at column.

I would like to sort users by the most recent post. For example, if:

user_id = 1, created 3 posts at: 17/05/2011, 19/05/2011, 21/05/2011
user_id = 2, created 1 post at: 22/05/2011
user_id = 3, created 2 posts at: 18/05/2011, 20/05/2011

the result should be:

user_id = 2
user_id = 1
user_id = 3

How would you achieve this in Rails 3 ?

Upvotes: 5

Views: 177

Answers (3)

Harish Shetty
Harish Shetty

Reputation: 64363

If you want to sort by the latest post you can rely on the fact that a row with the largest id is the latest:

Post.select(:user_id).group(:user_id).order("MAX(id) DESC")

This way you can use the existing primary key index on id column(and avoid having to add a new index on created_at column).

Upvotes: 1

apneadiving
apneadiving

Reputation: 115521

Should be something like:

Post.select(:user_id).group(:user_id).order("created_at DESC")

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

Not sure about the RoR part, but you're looking into a group by statement:

select user_id, max(created_at) as created_at
from posts
group by user_id
order by max(created_at) desc

Upvotes: 2

Related Questions