mysql - 用于选择聊天的单个 SQL 查询

Question

我需要选择所有聊天，现在我正在使用多个查询来选择它们，如下所示:

        $query_1 = mysqli_query($database, "SELECT chat_id, user_id_1, user_id_2
                                                     FROM chat
                                                     WHERE user_id_1 = '$user_id' OR user_id_2 = '$user_id')");

        if (mysqli_num_rows($query_1) > 0) {
            while ($row = mysqli_fetch_assoc($query_1)) {
                $chat_id = $row['chat_id'];
                $user_id_1 = $row['user_id_1'];
                $user_id_2 = $row['user_id_2'];

                if ($user_id_1 == $user_id)
                    $user_id_chat = $user_id_2;
                else
                    $user_id_chat = $user_id_1;

                $query_2 = mysqli_query($database, "SELECT username
                                                                FROM user
                                                                WHERE user_id = '$user_id_chat'");

                $row2 = mysqli_fetch_assoc($query_2);
                $username = $row2['username'];

                $query_3 = mysqli_query($database, "SELECT text,
                                                                (SELECT COUNT(message_id) FROM message WHERE chat_id = '$chat_id' AND user_id != '$user_id' AND seen = '0') AS unread_messages
                                                                FROM message
                                                                WHERE chat_id = '$chat_id'
                                                                ORDER BY message_id DESC
                                                                LIMIT 1");

                $row3 = mysqli_fetch_assoc($query_3);
                $text = $row3['text'];
                $unread_messages = $row3['unread_messages'];
            }
        }

有没有一种方法可以仅使用一个查询来获取所有这些($chat_id、$user_id_chat、$username、$text 和 $unread_messages)？

编辑:添加示例数据和预期输出:

Table user

user_id     username     ...
   1          User1      ...
   2          User2      ...
   3          User3      ...
   4          User4      ...


Table chat

chat_id     user_id_1     user_id_2     ...
   1            3            4          ...
   2            1            3          ...


Table message

message_id     chat_id     user_id     text     seen     ...
    1             1           4        Hi!       1       ...
    2             2           1        Hello     1       ...
    3             1           3        hi        0       ...
    4             2           3        Hi        0       ...
    5             2           3     How are you? 0       ...

如果 $user_id 为 1，则应该只有一次聊天，并且 $chat_id 的值应为 2，$user_id_chat 的值应为 3，$username 的值应为“User3”，$text 的值应为“你好吗？” $unread_messages 的数量应为 2。

Answer 1

http://sqlfiddle.com/#!9/8c6cb/8

$query_1 = mysqli_query($database, 
      "SELECT
         c.chat_id,
         u.username,
         COALESCE(m.text,'MY PLACEHOLDER'),
         COALESCE(um.unread_messages,0)
      FROM (
         SELECT chat_id, 
                IF(user_id_1 = '$user_id', user_id_2, user_id_1) user_id_chat
         FROM chat
         WHERE user_id_1 = '$user_id' OR user_id_2 = '$user_id'
      ) c
      LEFT JOIN user u
      ON u.user_id = c.user_id_chat
      LEFT JOIN (SELECT chat_id, SUM(seen = '0',1,0) unread_messages, MAX(message_id) max_id
           FROM message 
           WHERE  user_id != '$user_id' 
           GROUP BY chat_id) um
      ON um.chat_id = c.chat_id
      LEFT JOIN message m
      ON m.chat_id = c.chat_id
        AND m.message_id = um.max_id ");

if (mysqli_num_rows($query_1) > 0) {
  while ($row = mysqli_fetch_assoc($query_1)) {
    $chat_id = $row['chat_id'];
    $username = $row['username'];
    $text = $row ['text'];
    $unread_messages = $row['unread_messages'];
  }
}