如何根据引用表中的最后记录时间戳对 Ruby on Rails 中的记录进行排序

发布于 2025-01-10 12:53:28 字数 827 浏览 0 评论 0原文

我需要创建一个实时聊天应用程序，现在我有三个模型：

ChatRoom
ChatRoomMember
ChatRoomMessage

在这三个模型中，我使用 includes 和 references 来获取当前的 chat_rooms 列表登录用户。这是我写的代码。

@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)

然而，该命令并没有按照我的预期进行。我想要的是 chat_rooms 按该房间中最后一条消息的 created_at 列排序。我该怎么做？

这是数据库结构：

原文

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

ChatRoom
ChatRoomMember
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 :

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

π浅易 2025-01-17 12:53:28

使用关联来避免 where 'chat_room_members.user_id = ?', @current_user.id

这是我的建议，假设 User 具有如下所示的关联：

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)

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)

回复收藏 0 原文

迷路的信 2025-01-17 12:53:28

试试这个：

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

Try this:

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

回复收藏 0 原文

千と千尋 2025-01-17 12:53:28

感谢大家帮助我解决这个问题。我有自己的答案。就是这样。

SELECT chat_rooms.*, (SELECT chat_room_messages.created_at FROM chat_room_messages WHERE chat_room_messages.chat_room_id = chat_rooms.id ORDER BY chat_room_messages.id DESC LIMIT 1) AS last_message_at FROM chat_rooms INNER JOIN chat_room_members ON chat_room_members.chat_room_id = chat_rooms.id WHERE chat_room_members.user_id = #{@current_user.id} ORDER BY last_message_at DESC

我使用原始查询来解决这个问题。希望这可以帮助任何有需要的人！

Thanks for everyone has help me to solve this probel. I have my own answer. Here is it.

SELECT chat_rooms.*, (SELECT chat_room_messages.created_at FROM chat_room_messages WHERE chat_room_messages.chat_room_id = chat_rooms.id ORDER BY chat_room_messages.id DESC LIMIT 1) AS last_message_at FROM chat_rooms INNER JOIN chat_room_members ON chat_room_members.chat_room_id = chat_rooms.id WHERE chat_room_members.user_id = #{@current_user.id} ORDER BY last_message_at DESC

I use raw query for solve this problem. Hope this can help anyone who need it !

回复收藏 0 原文

~没有更多了~