Rails: Order a model by the last element of a has_many association

Question

I have two models: User and Message that are connected by a has_many relationship. I want to get a list of users sorted by the timestamp on their last message.

class User < ActiveRecord::Base
  has_many :messages
end
class Message < ActiveRecord::Base
  belongs_to :user
end

When I do this:

@users = User.includes(:messages).order('messages.created_at DESC').limit(5)

It seems to order the messages, grab the newest 5 messages, and then return the users associated with those. So the number of users can be less than 5. I want to make sure I get the 5 users.

I want the query to get the newest message for each request, order the last messages, and then return the users with the newest messages. So I want something like:

@users = User.includes(:messages).order( <messages.last.created_at DESC> )

Ideally, the solution would do the sorting on the database, because I want to use paginate. If it's relevant I'm using Postgres.

Answer 1

I would probably be preferential to the solution mentioned by phoet of adding an attribute to User such as last_message_posted_at and using touch: true to update that on message creation. That simplifies the query that has to be performed when you need to pull your list of users. This also allows a much more readable (IMO) chain for your application:

@users = User.all.order(:last_message_posted_at)
=> "SELECT \"users\".* FROM \"users\" ORDER BY \"users\".\"last_message_posted_at\" ASC"

This also allows you to add a nice and simple scope to your User model

scope: :by_recent_message, ->{ order(:last_message_posted_at) }
User.by_recent_message.limit(5)

It also depends when and how often this @users scope is being used. Adding a few ms to the message post time is preferable, to me, than a complicated SQL query each time the list of users is pulled.

-- Edit for those who aren't familiar with the touch syntax --

Documentation for touch: http://apidock.com/rails/v4.2.1/ActiveRecord/Persistence/touch

class User < ActiveRecord::Base
  has_many :messages
end
class Message < ActiveRecord::Base
  belongs_to :user, touch: true
end

And then make my query (to the user with most recent last message):

@user =  User.includes(:messages).order(updated_at: :desc )