Как лучше собрать все диалоги пользователя из таблицы сообщений в mysql?

Question

[Сергей Ванюшин]

Использую для хранения личных сообщений одну таблицу такого формата:
в таблице хранится id отправителя и id получателя, второй таблицы для хранения диалогов нет.

CREATE TABLE `v_user_messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `receiver_id` int(10) unsigned NOT NULL,
  `creation_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `text` text NOT NULL,
  `new` enum('0','1') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `receiver_id` (`receiver_id`),
  KEY `user_id_receiver_id` (`user_id`,`receiver_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Пытаюсь получить в один запрос: id пользователя, с которым идёт диалог, и дату последнего сообщения, с сортировкой по дате. Ещё бы и количество строк (сообщений) между пользователями получить.
Вот что я пробовал:

/* выводит точно и без лишнего мусора, только не удаётся отсортировать */
(SELECT user_id
FROM `v_user_messages`
WHERE receiver_id = 1
GROUP BY user_id
ORDER BY creation_datetime DESC)
UNION (SELECT receiver_id
FROM `v_user_messages`
WHERE user_id = 1
GROUP BY receiver_id
ORDER BY creation_datetime DESC);

/* неправильно считает количество строк и не получается отсортировать */
SELECT a.id, b.id, a.user_id as user_id, b.user_id as receiver_id, COUNT(*)
FROM v_user_messages a, v_user_messages b
WHERE a.user_id = 1 AND b.user_id != 1
GROUP BY user_id, receiver_id;

/* выводит лишние строки */
SELECT *
FROM v_user_messages
WHERE user_id = 1 OR receiver_id = 1
GROUP BY user_id, receiver_id
ORDER BY creation_datetime DESC;

Answer 1

[Сергей Ванюшин]

Решил так:
получение количества диалогов:

SELECT COUNT(*)
FROM (SELECT user_id
FROM v_user_messages
WHERE receiver_id = 1
UNION SELECT receiver_id
FROM v_user_messages
WHERE user_id = 1) as talks;

получение последних диалогов, даты последнего в них сообщения и количество непрочтенных для текущего юзера:

SELECT user_id, MAX(created_at) as updated_at, sum(new) as new_messages
FROM (SELECT user_id, created_at, new
FROM v_user_messages
WHERE receiver_id = 1
UNION SELECT receiver_id, created_at, 0
FROM v_user_messages
WHERE user_id = 1
ORDER BY created_at DESC) as talks
GROUP BY user_id
ORDER BY created_at DESC;

Answer 2

[Yuri Morozov]

Что нужно знать:

1. при группировке следует указывать либо аггрегирующую функцию, либо прописывать поле в условиях, либо не выбирать поле вообще, иначе результат будет неопределен (MySQL такое прощает, но вообще говоря нельзя так).
К примеру: SELECT MAX(creation_datetime) ... GROUP BY user_id, receiver_id

2. По агрегатам можно не только сортировать, но и накладывать на них условия.
К примеру:
SELECT MAX(creation_datetime) AS m ... GROUP BY user_id, receiver_id ORDER BY m desc HAVING m > ...

3. Вы задолбаетесь каждый раз писать creation_datetime, от этого болят руки и развивается воспаление карпального канала. Общепринятыми названиями подобных полей являются created_at, updated_at, deleted_at, ...

4. ENUM(0, 1) - это пять :)

Comments

[Сергей Ванюшин]

да у меня проблема та что может быть запись (user_id=1, receiver_id = 2), а может быть обратная. и нужно их как-то посчитать и найти ту, у которой время больше.
3. thanks, поправлю.

[Yuri Morozov]

@wapmorgan Ну так я ж и говорю,
SELECT MAX(creation_datetime) AS m
WHERE user_id = 1 OR receiver_id = 1
GROUP BY user_id, receiver_id
ORDER BY m desc
LIMIT 1

Answer 3

[LoveTime]

Когда я решал эту задачу, для меня единственной проблемой было то. что у меня были лишние записи, например (sender_id, recipient_id) были (1,2) и (2,1) это были самые последние записи по времени с данными значениями, это достигалось с помощью Group by sender_id, recipient_id и max(time) в SELECT. Но одна запись была лишняя. Я искал как объединить эти две группы. Можно заметить, что одна из цифер всегда будет присутствовать в записи( в условии прописано, where recipient_id == @user_id and sender_id == @my_user, поэтому можно легко объединить эти две группы в одну по сумме этих чисел и не будет никаких конфликтов. Мне кажется, что легче не придумаешь. Весь запрос:
SELECT *, MAX(time), sender_id + recipient_id as my_sum FROM chat
WHERE sender_id == 1 or recipient_id == 1
GROUP BY my_sum
ORDER BY time DESC