如何用MySQL单查询获取用户所有会话及会话对方的用户信息？

你可以通过关联users表来一次性获取会话对方的完整用户数据，同时保留会话的核心信息。以下是修改后的代码和SQL逻辑：

修改后的代码 #

const getChats = (req, res) => {
  const userId = req.params.userId;
  db.query(
`SELECT DISTINCT
    c.id AS conversation_id,
    CASE WHEN c.user1_id = ? THEN c.user2_id ELSE c.user1_id END AS partner_id,
    u.id AS user_id,
    u.name
FROM conversations c
JOIN users u ON u.id = CASE WHEN c.user1_id = ? THEN c.user2_id ELSE c.user1_id END
WHERE ? IN (c.user1_id, c.user2_id)`,
    [userId, userId, userId],
    (e, r) => {
      if (e) {
        return res.status(500).send({ error: e.message });
      }
      res.send(r);
    }
  );
};

module.exports = { getChats };

关键逻辑说明 #

1. 关联用户表：通过JOIN users，用CASE语句判断出的会话对方ID（partner_id）匹配users.id，直接拉取对方的用户数据（这里示例取了id和name，你可以根据需求添加其他字段）
2. 保留会话ID：返回conversation_id方便后续关联messages表查询会话消息
3. 去重处理：DISTINCT确保不会返回重复的会话-用户组合
4. 错误处理：新增错误捕获，避免直接抛出服务器异常

扩展：获取会话最新消息（可选） #

如果需要同时展示每个会话的最后一条消息，可以进一步关联messages表：

const getChats = (req, res) => {
  const userId = req.params.userId;
  db.query(
`SELECT DISTINCT
    c.id AS conversation_id,
    CASE WHEN c.user1_id = ? THEN c.user2_id ELSE c.user1_id END AS partner_id,
    u.id AS user_id,
    u.name,
    COALESCE(m.message, '暂无消息') AS last_message,
    m.date AS last_message_date
FROM conversations c
JOIN users u ON u.id = CASE WHEN c.user1_id = ? THEN c.user2_id ELSE c.user1_id END
LEFT JOIN (
    SELECT conversationId, message, date
    FROM messages
    WHERE (conversationId, date) IN (
        SELECT conversationId, MAX(date)
        FROM messages
        GROUP BY conversationId
    )
) m ON m.conversationId = c.id
WHERE ? IN (c.user1_id, c.user2_id)`,
    [userId, userId, userId],
    (e, r) => {
      if (e) {
        return res.status(500).send({ error: e.message });
      }
      res.send(r);
    }
  );
};

这个SQL通过子查询先找出每个会话的最新消息，再关联到主查询中，COALESCE用来处理没有消息的会话，避免返回NULL。

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