mysql - 获取对话和最后一条消息查询

Question

我正在尝试获取与相关最后一条消息的对话，并按时间和是否阅读消息对它们进行排序。让我们来展示一下我的逻辑。

我创建了 3 个表:inbox_join/inbox_msg/users

在第一个表“收件箱加入”中，我有有关谁进行积极讨论的数据。在本例中，我们有 id_user - “1” 和 id_user_2 - “4”，他们进行了对话。

在 inbox_msg 表中，我有短信、显示消息的 ID 对话以及其他易于理解的字段。

收件箱连接表

Inbox_msg 表

用户表

我做了一个运行良好的查询，但我的问题是我无法在 inbox_msg 表中包含发生的_at。我想找到一个更好的解决方案来获得我想要的结果，但我无法订购我想要的方式。

这是我的查询

SELECT DISTINCT (
inbox_join.id_conversation
), user_chat.name AS name_conv, user_chat.surname AS surname_conv, user_chat.username as username_conv, user_chat.id as id_chat, image_upload.name_image, (

SELECT DISTINCT (
message
)
FROM inbox_msg
WHERE inbox_join.id_conversation = inbox_msg.id_conversation
ORDER BY occured_at DESC 
LIMIT 1
) AS last_msg, users.name, users.surname
FROM inbox_join

INNER JOIN users ON users.id = inbox_join.id_user
INNER JOIN users AS user_chat ON user_chat.id <> 1 AND (inbox_join.id_user_2 = user_chat.id || inbox_join.id_user = user_chat.id)
INNER JOIN image_upload ON image_upload.id_image = user_chat.profile_image
WHERE inbox_join.id_user = 1
OR inbox_join.id_user_2 = 1

选择有关用户1的对话的结果:

id_conversation | id_user | name | surname | username | last_msg | occured_at_last_msg | read_msg |

       1           4         E        S         E           Yes            1380724676        0
       4           5         G        E         K           Good           1380724675        0

Answer 1

查询:

SELECT  im.id_conversation,
        im.id_user,
        u.name,
        u.surname,
        u.username,
        im.message AS last_msg,
        im.occured_at AS occured_at_last_msg,
        im.read_msg 
FROM inbox_msg im
JOIN users u
ON u.id_user = im.id_user
JOIN (SELECT id_conversation,
             MAX(occured_at) AS occured_at
      FROM inbox_msg
      GROUP BY id_conversation) im2
ON im2.id_conversation = im.id_conversation
AND im2.occured_at = im.occured_at
ORDER BY im.occured_at DESC