php - 查询以在 'threaded' View 中获取最近的消息

Question

我正在尝试重建现有的消息系统，使其看起来类似于 Twitter 的 DM 服务。目前我只向我的用户显示未读消息，但我想在单个“线程”中显示 2 个用户之间发送的消息，如下所示:

这是我当前的数据库结构和一些示例数据: http://sqlfiddle.com/#!2/574e3/1/0

我尝试通过分解查询来逐步构建查询，但我很困惑。这是我已采取的步骤的列表:

<强>1。获取两个用户之间发送的消息列表

SELECT * FROM uc_user_messages
WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1

<强>2。显示两个用户之间“对话”中的最后一条消息

SELECT * FROM uc_user_messages
WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1
ORDER BY timestamp DESC

<强>3。按 user_id 对对话进行分组(显然，这将是生产应用程序中带有一堆连接的用户名)。

SELECT * FROM uc_user_messages
WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1
GROUP BY uc_user_messages.user_id_1 AND uc_user_messages.user_id_2
ORDER BY timestamp DESC 

这就是我卡住的地方......问题是将显示两个用户之间的对话中发送的第一条消息，而不是最近的消息:http://sqlfiddle.com/#!2/942fb/2

我该怎么办？希望我不必对数据库设计进行任何重大更改。任何帮助是极大的赞赏。

干杯，

乔尔

Answer 1

SELECT  *
FROM    uc_user_messages
WHERE   (LEAST(user_id_1, user_id_2),GREATEST(user_id_1, user_id_2),TIMESTAMP) 
        IN 
        (
            SELECT  LEAST(user_id_1, user_id_2) AS x,
                    GREATEST(user_id_1, user_id_2) AS y,
                    max(TIMESTAMP) AS msg_time
            FROM    uc_user_messages
            GROUP   BY x, y
        );

• SQLFiddle Demo

输出

╔════╦═══════════╦═══════════╦═════════════════╦════════════╦══════╗
║ ID ║ USER_ID_1 ║ USER_ID_2 ║     MESSAGE     ║ TIMESTAMP  ║ READ ║
╠════╬═══════════╬═══════════╬═════════════════╬════════════╬══════╣
║  3 ║         1 ║         2 ║ third message   ║ 1366577336 ║    1 ║
║  4 ║         2 ║         0 ║ a message reply ║ 1366577346 ║    1 ║
╚════╩═══════════╩═══════════╩═════════════════╩════════════╩══════╝