What is the best way to collect all user dialogs from message table in mysql?

Question

MySQL

I use one table of this format for storing personal messages:
the table stores the sender's id and the recipient's id, there is no second table for storing dialogs.

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;

I'm trying to get in one request: the id of the user with whom there is a dialogue, and the date of the last message, sorted by date. Still and an amount of lines (messages) between users to receive.
Here's what I've tried:

/* выводит точно и без лишнего мусора, только не удаётся отсортировать */
(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

I solved it this way:
getting the number of dialogs :

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;

getting the latest dialogs, the date of the last message in them and the number of unread messages for the current user :

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

What you need to know:
1. when grouping, you should specify either an aggregating function, or specify a field in the conditions, or do not select a field at all, otherwise the result will be undefined (MySQL forgives this, but generally speaking it is not possible).
For example: SELECT MAX(creation_datetime) ... GROUP BY user_id, receiver_id
2. You can not only sort by aggregates, but also impose conditions on them.
For example:
SELECT MAX(creation_datetime) AS m ... GROUP BY user_id, receiver_id ORDER BY m desc HAVING m > ...
3. You get tired of writing creation_datetime every time, this hurts your hands and develops inflammation of the carpal tunnel. Common names for such fields are created_at, updated_at, deleted_at, ...
4. ENUM(0, 1) is five :)