如何获取指定用户参与对话中的最后一条消息（PostgreSQL）

一、解决SQL查询问题 #

要获取用户1参与的每个对话的最后一条消息并转为JSON格式，可通过以下两种PostgreSQL原生方案实现：

方法1：使用DISTINCT ON（高效简洁，PostgreSQL专属） #

SELECT
  c.id AS listing_conversation_id,
  to_jsonb(cm.*) AS latest_message
FROM my_schema.conversation c
JOIN (
  -- 按对话分组，每组取创建时间最晚的消息；若时间相同则取id最大的（自增主键保证顺序）
  SELECT DISTINCT ON (conversation_id) *
  FROM my_schema.conversation_message
  WHERE from_user_id = 1 OR to_user_id = 1
  ORDER BY conversation_id, created_at DESC, id DESC
) cm ON cm.conversation_id = c.id;

方法2：使用窗口函数ROW_NUMBER() #

SELECT
  c.id AS listing_conversation_id,
  to_jsonb(cm.*) AS latest_message
FROM my_schema.conversation c
JOIN (
  -- 给每个对话的消息按时间倒序打行号，仅保留行号为1的最新消息
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at DESC, id DESC) AS rn
  FROM my_schema.conversation_message
  WHERE from_user_id = 1 OR to_user_id = 1
) cm ON cm.conversation_id = c.id AND cm.rn = 1;

说明：

• 两种方案都先筛选出用户1参与的消息，再按对话维度提取最新条目；
• 加入id DESC是为了处理多条消息created_at完全相同的边界情况；
• to_jsonb()相比to_json()支持更多JSON操作，推荐使用。

二、数据模型合理性评估 #

现有模型的优点 #

• 遵循数据库设计范式，表结构拆分清晰，避免数据冗余；
• 外键约束完整，能保证数据一致性（如消息关联的对话、用户必须存在）；
• 基础结构满足单聊场景的核心业务需求。

可优化方向 #

1. 补充业务必需字段：
   • conversation表仅保留主键id，实际业务中需添加对话创建时间、群聊名称、归档状态等字段；
   • user表仅存id，缺少用户名、头像、联系方式等核心用户信息。
2. 适配群聊场景：
   当前模型的conversation_message.to_user_id仅支持单聊，若需拓展群聊，需调整模型：
   • 新增conversation_user关联表，记录对话与参与用户的关系；
   • conversation_message可移除to_user_id，或保留并允许为空，同时新增message_type字段标记单聊/群聊。
3. 优化查询性能：
   针对「按对话查最新消息」这类高频查询，建议给conversation_message建立组合索引：

   -- 加速按对话筛选最新消息
   CREATE INDEX idx_cm_conversation_created_at ON my_schema.conversation_message (conversation_id, created_at DESC, id DESC);
   -- 加速按用户筛选参与的消息
   CREATE INDEX idx_cm_user_ids ON my_schema.conversation_message (from_user_id, to_user_id);
   

内容的提问来源于stack exchange，提问作者Mike K