Laravel实现用户收件箱：高效获取所有对话及单条最新消息

前提假设（基于常见私信表结构） #

先明确三张表的核心字段（如果你的表结构不同，可对应调整）：

• threads: id（对话ID）、updated_at（对话更新时间，用于排序）
• participants: thread_id（关联对话ID）、user_id（关联用户ID）
• messages: id、thread_id、sender_id、content、created_at（消息发送时间）
• 额外需要users表存储用户的id、name、avatar（姓名、头像）

核心SQL查询（以当前登录用户ID=123为例） #

SELECT
    t.id AS thread_id,
    -- 获取对话中的对方用户信息
    CASE
        WHEN p1.user_id = 123 THEN u2.name ELSE u1.name
    END AS partner_name,
    CASE
        WHEN p1.user_id = 123 THEN u2.avatar ELSE u1.avatar
    END AS partner_avatar,
    -- 获取最新一条消息的内容和发送者
    m.content AS latest_message_content,
    m.sender_id AS latest_message_sender_id,
    m.created_at AS latest_message_time,
    t.updated_at AS thread_updated_time
FROM threads t
-- 关联参与者表，找到当前用户参与的所有对话
JOIN participants p1 ON t.id = p1.thread_id AND p1.user_id = 123
-- 关联对话中的另一个参与者
JOIN participants p2 ON t.id = p2.thread_id AND p2.user_id != 123
-- 关联对方用户的信息
JOIN users u1 ON p1.user_id = u1.id
JOIN users u2 ON p2.user_id = u2.id
-- 关联每个对话的最新消息（用窗口函数筛选）
JOIN (
    SELECT
        thread_id,
        content,
        sender_id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY thread_id ORDER BY created_at DESC) AS rn
    FROM messages
) m ON t.id = m.thread_id AND m.rn = 1
-- 按对话更新时间倒序（最新对话在前）
ORDER BY t.updated_at DESC;

性能优化关键点 #

• 索引优化：
  • 给participants表创建复合索引：(user_id, thread_id)，快速定位当前用户参与的所有对话
  • 给messages表创建复合索引：(thread_id, created_at DESC)，加速每个对话最新消息的查询
  • 给threads表的updated_at字段创建索引，优化排序效率
• 避免N+1查询：不要先查所有对话ID，再循环查每个对话的最新消息和对方用户，用一次SQL关联完成所有数据获取
• 字段按需查询：只查询需要的字段（比如不要SELECT *），减少数据传输量

特殊场景处理 #

• 如果对话支持多参与者（不止2人），需要调整p2的关联逻辑，比如用GROUP_CONCAT拼接所有其他参与者信息，但你需求是标准收件箱（一对一），所以上述SQL适用
• 如果存在对话还没有消息的情况，用LEFT JOIN替代JOIN关联messages子查询，此时latest_message相关字段会为NULL，前端可以显示“暂无消息”

内容的提问来源于stack exchange，提问作者0x9342812j1o