如何获取当前用户与每个聊天对象的最新消息？

需求 #

获取当前用户与每个聊天对象的最新消息。

原SQL代码 #

SELECT messages.id, messages.body, messages.created_at, messages.is_seen,
            o.username AS from_username, o.image_url AS from_image_url, o.gender AS from_gender,
            c.username AS to_username, c.image_url AS to_image_url, c.gender AS to_gender,

            (SELECT count(messages.id) from messages where messages.to_user_id = 1) AS unread_message_count

            FROM messages

            LEFT OUTER JOIN users AS o ON messages.from_user_id = o.id
            LEFT OUTER JOIN users AS c ON messages.to_user_id = c.id

            WHERE messages.id IN
                (SELECT MAX(messages.id) FROM messages
                    WHERE (messages.to_user_id = 1 OR messages.from_user_id = 1)
                    GROUP BY messages.from_user_id, messages.to_user_id)
            OFFSET 0 LIMIT 5

问题 #

该SQL会为每个聊天会话返回两条记录。

期望结果（以user_id=1为例） #

id ... from_user_id to_user_id
9 |    | 2          | 1
8 |    | 1          | 3
7 |    | 4          | 1

测试数据 #

with t(id,body,created_at,is_seen,from_user_id,to_user_id) as (values
(1,'hello how are you man','2022-08-12 03:07:15',false,1,4),
(2,'are you okay','2022-08-12 03:08:14',false,1,4),
(3,'yesIknow you man','2022-08-12 03:08:25',false,1,4),
(4,'where are you from','2022-08-12 03:13:45',false,1,4),
(5,'Iamfromnothing','2022-08-12 03:13:51',false,1,4),
(6,'areyouok?','2022-08-12 03:13:58',false,1,4),
(7,'yesitis','2022-08-12 05:37:09',false,4,1),
(8,'thankyousomuch','2022-08-12 07:09:36',false,1,3),
(9,'youareverywelcome','2022-08-12 07:10:10',false,2,1)
)
select * from t 

解决方案 #

问题根源在于原SQL的分组条件GROUP BY messages.from_user_id, messages.to_user_id会将(1,4)和(4,1)视为两个独立分组，导致每个聊天会话返回两条最新消息。需要将聊天双方的ID统一为一个“会话标识”，让双向聊天归为同一分组。

修改后的SQL如下：

SELECT messages.id, messages.body, messages.created_at, messages.is_seen,
       o.username AS from_username, o.image_url AS from_image_url, o.gender AS from_gender,
       c.username AS to_username, c.image_url AS to_image_url, c.gender AS to_gender,
       (SELECT count(m.id) FROM messages m WHERE m.to_user_id = 1 AND NOT m.is_seen) AS unread_message_count
FROM messages
LEFT OUTER JOIN users AS o ON messages.from_user_id = o.id
LEFT OUTER JOIN users AS c ON messages.to_user_id = c.id
WHERE messages.id IN (
    SELECT MAX(m.id)
    FROM messages m
    WHERE m.to_user_id = 1 OR m.from_user_id = 1
    GROUP BY 
        LEAST(m.from_user_id, m.to_user_id),
        GREATEST(m.from_user_id, m.to_user_id)
)
OFFSET 0 LIMIT 5;

关键修改点 #

• 子查询分组条件改为LEAST(m.from_user_id, m.to_user_id)和GREATEST(m.from_user_id, m.to_user_id)，让(1,4)和(4,1)归为同一分组，仅保留该分组下的最大ID（即最新消息）。
• 修正未读消息统计逻辑，原SQL统计所有发给user_id=1的消息数，改为统计未读消息（NOT m.is_seen），更贴合实际需求。

内容的提问来源于stack exchange，提问作者Hadi Farhadi