How to Sort Record in Ruby on Rails based on Last Record Timestamp from References Table

Question

I need to create a live chat app and now I have three models :

1. ChatRoom
2. ChatRoomMember
3. ChatRoomMessage

From this three models, I use includes and references to get the list of chat_rooms for current login user. Here is the code that I have wrote.

@chat_rooms = ChatRoom.includes(:members).references(:members)
@chat_rooms = @chat_rooms.includes(:messages).references(:messages)
@chat_rooms = @chat_rooms.where 'chat_room_members.user_id = ?', @current_user.id
@chat_rooms = @chat_rooms.order 'chat_room_messages.created_at DESC'
@chat_rooms = @chat_rooms.limit(limit).offset(offset)

However, the order didn't work as I expected. What I want is that the chat_rooms are sorted by created_at column from the last message in that room. How can I do this ?

Here is the database structure :

Answer 1

Use association to avoid where 'chat_room_members.user_id = ?', @current_user.id

Here is my suggestion, assuming User has associations looking like:

class User
  has_many :chat_room_members
  has_many :chat_rooms, through: :chat_room_members
end

# list only rooms with at least on message
@chat_rooms = @current_user.chat_rooms.joins(:messages).order('chat_room_messages.created_at DESC').limit(limit).offset(offset)

# list all rooms even if there is no message attached
@chat_rooms = @current_user.chat_rooms.distinct.left_joins(:messages).order('chat_room_messages.created_at DESC').limit(limit).offset(offset)

Answer 2

Try this:

ChatRoom.includes(:messages).order('chat_room_messages.created_at DESC')