Как сгруппировать диалоги по двум полям в MySQL через GroupBY?

Question

[freeeeez]

Есть таблица dialogs с диалогами по товарам пользователя, поля таблицы id, tovar_id, sender_id, recipient_id, message как вывести список диалогов чтобы они не повторялись? Каждый диалог содержит ID товара, ID того кто написал и ID кому он написал. Соответственно если идет переписка между пользователями id=1 и id=2, то в таблицы будут следующие строки:
- id=1, tovar_id=1, sender_id=1, recipient_id=2, message='привет'
- id=2, tovar_id=1, sender_id=2, recipient_id=1, message='hello'
Если group by tovar_id, sender_id, recipient_id то выводится две строки, хотя это один диалог. По tovar_id=1может вестись переписка с другими пользователями,
- id=3, tovar_id=1, sender_id=101, recipient_id=1, message='new message'
поэтому не подходит просто группировка по tovar_id

Answer 1

[alexalexes]

Пробуйте этот запрос с вычисленным псевдопараметром dialog_hold.
Суть в том, что этот параметр одинаковый для переписки пользователя 1 и пользователя 2 в ту и другую сторону.
Должно получаться число 1000002 при sender_id=1, recipient_id=2 и при sender_id=2, recipient_id=1.
По этому параметру можно отсортировать переписку так, чтобы беседы пользователей шли по порядку.
Значение 1000000 должно быть заведомо большое, чем возможно число пользователей в вашей системе в любой исторический период. Однако, max(user_id) * 1000000 + max(user_id) никогда не должно превышать макс. целочисленное значение, с которым может работать СУБД (верхний предел id пользователей, когда этот финт ушами способен работать).

select a.*
from (select d.id, d.tovar_id, d.sender_id, d.recipient_id, d.message,
             case
               when d.sender_id < d.recipient_id
               then d.sender_id * 1000000 + d.recipient_id
               else d.recipient_id * 1000000 + d.sender_id
             end dialog_hold
        from dialogs d
       where d.tovar_id = :tovar_id
         and (d.sender_id = :user_id or d.recipient_id = :user_id)
      ) a
order by  a.tovar_id, a.dialog_hold, a.id

Comments

[freeeeez]

изменил описание

[alexalexes]

Набросал примерное решение, пробуйте.

[freeeeez]

применил запрос - выводит все диалоги и 1-2 и 2-1 как отдельные

[freeeeez]

последняя строка:
group by a.tovar_id, a.dialog_hold

работает

[alexalexes]

С помощью запроса:

INSERT INTO `dialogs`(`tovar_id`, `sender_id`, `recipient_id`, `message`)
VALUES (1, 1, 2, 'Привет, Алиса, я Боб. Как утюг?'),
(1, 2, 1, 'Привет, Боб, я Алиса. Утюг хорош!'),
(1, 1, 3, 'Привет, Ева, это Боб. Нормальный пылесос?'),
(1, 2, 1, 'Боб, я Алиса. Как выключить утюг?'),
(1, 3, 1, 'Боб, я Ева. Пылесос не работает.')

накидал тестовую выборку:

Как видно, есть накладки в последовательности сообщений пользователей 1, 2 и 1, 3.
Далее, взял написанный запрос, убрал параметры выбора сообщений по конкретному товару, и тех, где участвует конкретный пользователь:

select a.*
from (select d.id, d.tovar_id, d.sender_id, d.recipient_id, d.message,
             case
               when d.sender_id < d.recipient_id
               then d.sender_id * 1000000 + d.recipient_id
               else d.recipient_id * 1000000 + d.sender_id
             end dialog_hold
        from dialogs d
       /*where d.tovar_id = :tovar_id  -- по конкретному товару
         and (d.sender_id = :user_id or d.recipient_id = :user_id) -- и где участвует пользователь user_id
      */
      ) a
order by  a.tovar_id, a.dialog_hold, a.id

Получили выборку:

Как видно, получилось сгруппировать сообщения по беседкам dialog_hold.
Если, допустим, в PHP собрать двумерный массив по ассоц. ключам tovar_id и dialog_hold, то в каждом элементе out (отдельный товар) получим массив беседок, а каждой беседке - цепочку сообщений по данному товару между двумя пользователями.

$out = [];
foreach($rows as $row) //$rows - это выборка по результату запроса
{
  $out[$row['tovar_id']][$row['dialog_hold']][] = $row;  
}

Answer 2

[Руслан .]

Можно вот так попробовать:

select dd.tovar_id, dd.sender_id
  from dialogs dd
 where not exists(select 1
                    from dialogs ds
                   where ds.tovar_id     = dd.tovar_id
                     and ds.recipient_id = dd.dd.sender_id
                     and ds.id           < dd.id)
 group by dd.tovar_id, dd.sender_id

Тут будут все диалоги по всем товарам.
Если нужно фильтровать по пользователю или товару, можно это сделать задав секцию where