NestJS+Sequelize按最新消息排序查询联系人接口问题

DevOps 小满

2026-8-18

解决方案

要实现按双方最后一条消息的创建时间对联系人排序，核心是先计算当前用户与每个联系人之间的最新消息时间，再以此作为排序依据。以下是两种可行的原生SQL方案：

基础版（筛选所有有消息交互的用户）

假设当前用户ID为1，执行以下查询：

SELECT 
    u.id,
    u.email,
    u.created_at AS user_created_at,
    MAX(cm.created_at) AS last_message_time
FROM users u
JOIN chat_messages cm 
    ON (cm.sender_id = u.id AND cm.recipient_id = 1) 
    OR (cm.recipient_id = u.id AND cm.sender_id = 1)
WHERE u.id != 1
GROUP BY u.id, u.email, u.created_at
ORDER BY last_message_time DESC;

逻辑说明

通过JOIN关联用户表与消息表，匹配当前用户作为发送方或接收方的所有消息记录
WHERE u.id != 1排除当前用户自身
GROUP BY按联系人分组，用MAX(cm.created_at)提取每组（即当前用户与该联系人）的最后一条消息时间
ORDER BY last_message_time DESC实现最新对话优先排序

严格版（仅保留互发过消息的用户）

如果需要严格符合“互发过消息”的定义（双方都给对方发过消息），可以用EXISTS子句筛选：

SELECT 
    u.id,
    u.email,
    u.created_at AS user_created_at,
    MAX(cm.created_at) AS last_message_time
FROM users u
WHERE EXISTS (
    SELECT 1 FROM chat_messages 
    WHERE sender_id = u.id AND recipient_id = 1
)
AND EXISTS (
    SELECT 1 FROM chat_messages 
    WHERE recipient_id = u.id AND sender_id = 1
)
JOIN chat_messages cm 
    ON (cm.sender_id = u.id AND cm.recipient_id = 1) 
    OR (cm.recipient_id = u.id AND cm.sender_id = 1)
GROUP BY u.id, u.email, u.created_at
ORDER BY last_message_time DESC;