mysql - MySQL：从相互关联的 3 个或 4 个表中获取最新的未读消息

Question

谢谢您的帮助！我对 SQL 很陌生。感谢您通过这么长的解释与我相处。只是想确保我很详细：

我正在构建一个消息传递系统。我需要做的是按DESC顺序检索 1 个或多个指定用户的最新未读消息。

这是我当前构建系统的方式：

Aconversation可以在 2 个或更多用户和/或组之间创建，并且每个用户和/或组都与它相关联conversation。中的用户/组conversation可以相互发送messages。

发送a 时message，它与conversation. 然后， 中的每个用户/组conversation都与该新的message.

所以为了存储所有这些，我有以下表格：

conversation
message
assoc_message__conversation
assoc_user_group__conversation
assoc_user_group__message

我有以下 db-fiddle 与我的表结构和插入其中的示例数据：

https://www.db-fiddle.com/f/s5bU15eVSxb5TDXy2GVtUS/0

如您所见，我进行了 4 个对话，每个对话有 2 条消息——“第一条消息”和“最后一条消息”。

假装我是 useru-1000001和 group g-1000001。您可以看到此用户和组一起在对话 1、2 和 3 中。assoc_user_group__message通过查看表格，您还可以看到此用户和组已阅读对话 1 中的两条消息。

我需要做的是能够指定u-1000001和/或g-1000001，并检索指定用户和/或组所在的每个对话的最新未读消息，按最新未读消息排序DESC（按message_id，不是send_datetime）。我还需要能够指定message_id在第一个请求之前检索 X 条未读消息，或在第一个请求（message_id < 4或message_id > 4）之后检索 X 条新的未读消息。

我需要为每条未读消息检索以下字段：conversation_id, message_id, message, send_datetime, raw_id, user_type. 您可以看到这些字段跨越 4 个不同的表：message、assoc_user_group__message、assoc_user_group__conversation、assoc_message__conversation

u-1000001以下是如果我输入and g-1000001（省略字段）应该检索的示例send_datetime：

| conversation_id | message_id | message  | raw_id  | user_type |
-----------------------------------------------------------------
|               3 |          6 | Last Msg | 1000001 | user      |
-----------------------------------------------------------------
|               2 |          4 | Last Msg | 1000001 | group     |
-----------------------------------------------------------------
|               2 |          4 | Last Msg | 1000001 | user      |
-----------------------------------------------------------------

如果我只输入u-1000001，我会得到相同的结果，只是没有组行。您可以看到conversation_id1 被省略，因为用户和组都已阅读该对话中的消息。

我知道这是一个复杂的问题，但我非常感谢您的时间和帮助！

标签： mysql