Запрос mysql, как объединить сообщения в чаты?

Question

[viktorross]

Здравсnвуйте, подскажите пожалуйста как правильно сделать запрос в бд, чтобы сгруппировать сообщения людей?

Есть образно говоря таблица class_chat, там столбцы from_user_id, to_user_id, chat_id и msg

как мне выдать все сообщения , если например существует 4 условия,
from_user_id = 111 and to_user_id= 222 and chat_id = 1
to_user_id = 111 and from_user_id= 222 and chat_id = 1

from_user_id = 333 and to_user_id= 444 and chat_id = 2
to_user_id = 444 and from_user_id= 333 and chat_id = 2

по факту это 2 диалога , как мне вывести не 4 записи , а две уникальных ? т.е два сгруппированных чата, и возможно ли это без условия chat_id? Что-то я не совсем понимаю как это сделать

Comments

[Дмитрий]

Сделать не from_user_id, to_user_id а сделать таблицу chat_members где будет chat_id, user_id и две строки

[Akina]

GROUP BY LEAST(from_user_id, to_user_id), 
         GREATEST(from_user_id, to_user_id)

Answer 1

[alvi31182v]

GROUP BY GROUP_CONCAT для объединения сообщений в одну строку, упорядоченную по времени т.е если у тебя есть поле timestamp или msg_time если ты не хочешь использовать chat_id, то можно попробовать сделать следующий запрос

GROUP BY chat_id - таким образом, строки с одинаковым chat_id будут объединены в одну группу

"Функция LEAST выбирает минимальное из значений. Значениями могут выступать поля, а также строки и числа"
"Функция GREATEST выбирает максимальное из значений / помогает упорядочивать пары пользователей"

Комбинация LEAST и GREATEST гарантирует уникальность групировки чатов игнорируя порядок пар пользоваталей чатов.
Вобщем есть набросок, дальше можно уже самому накидать и поиграться.

$sql = "
    SELECT 
        MIN(from_user_id) as user1,
        MAX(from_user_id) as user2,
        chat_id,
        GROUP_CONCAT(msg ORDER BY msg_time) as messages
    FROM
        class_chat
    WHERE
        (from_user_id = :user1 AND to_user_id = :user2)
        OR
        (to_user_id = :user1 AND from_user_id = :user2)
        OR
        (from_user_id = :user3 AND to_user_id = :user4)
        OR
        (to_user_id = :user3 AND from_user_id = :user4)
    GROUP BY
        chat_id, LEAST(from_user_id, to_user_id), GREATEST(from_user_id, to_user_id)
";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':user1' => 111, // тут айдишники пользаков
    ':user2' => 222,
    ':user3' => 333,
    ':user4' => 444,
]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Обработка результата
    echo "Chat ID: " . $row['chat_id'] . "\n";
    echo "User 1: " . $row['user1'] . "\n";
    echo "User 2: " . $row['user2'] . "\n";
    echo "Messages: " . $row['messages'] . "\n";
    echo "-----------------\n";
}