基于MySQL的群聊系统：获取带未读消息的联系人列表需求

Alright, let's break down how to build this query for your specific scenario (user ID 1, company ID 3). The goal is to get a list of all your contacts from shared chat rooms, along with how many unread messages you have from each, plus some context like last message details.

Final Query #

SELECT
    u.id AS contact_id,
    CONCAT(u.firstname, ' ', u.lastname) AS contact_name,
    u.email AS contact_email,
    COUNT(rm.id) AS unread_messages_count,
    MAX(rm.created_at) AS last_message_timestamp,
    MAX(rm.message) AS last_message_content
FROM
    users u
-- 关联联系人所在的聊天房间
JOIN room_users ru ON u.id = ru.user_id
-- 筛选出仅和你（ID=1）共享的房间
JOIN room_users my_rooms ON my_rooms.room_id = ru.room_id 
    AND my_rooms.user_id = 1
-- 关联消息表，统计你未读的消息（仅统计你最后在线时间之后发送的消息）
LEFT JOIN room_messages rm 
    ON rm.room_id = ru.room_id 
    AND rm.user_id = u.id 
    AND rm.created_at > my_rooms.online
WHERE
    u.id != 1 -- 排除你自己
    -- 如果需要限定仅同公司联系人，取消下面注释
    -- AND u.company_id = 3
GROUP BY
    u.id, u.firstname, u.lastname, u.email
ORDER BY
    unread_messages_count DESC,
    last_message_timestamp DESC;

关键逻辑说明 #

• 房间关联: 通过两次关联room_users表，先找到联系人所在的所有房间，再筛选出你也加入的房间，确保只拉取和你有共同群聊的联系人。
• 未读消息统计: 用LEFT JOIN关联消息表时，添加了rm.created_at > my_rooms.online的条件——只统计你在该房间最后上线之后，联系人发送的消息，这样得到的就是真正的未读数量。
• 附加上下文: 额外加入了最后一条消息的时间和内容，方便你快速了解和每个联系人的最新对话情况。
• 排序规则: 优先按未读消息数降序排列，让有未读消息的联系人排在最前面；再按最后消息时间排序，保证最新的互动优先展示。

边界情况处理 #

• 如果你没有加入任何群聊，查询会返回空列表（符合逻辑，没有可展示的联系人）。
• 没有未读消息的联系人，unread_messages_count会显示为0，依然会出现在全量联系人列表中。
• 如果需要仅展示同公司（company_id=3）的联系人，只需取消WHERE子句中对应注释即可。

内容的提问来源于stack exchange，提问作者Dorin Musteața