Как сделать правильно группировку?

Question

[Артём]

Пытаюсь сделать простой обмен сообщениями между пользователями.
Набросал следующую табличку:

таблица mails

CREATE TABLE `mails` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `sender_id` bigint unsigned DEFAULT NULL COMMENT 'ID отправителя',
 `recipient_id` bigint unsigned DEFAULT NULL COMMENT 'ID получателя',
 `message` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Сообщение',
 `system` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Системное ли сообщение',
 `read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Прочитано ли сообщение',
 `trash` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Удалено ли сообщение',
 `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата добавления',
 `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Дата последнего обновления',
 PRIMARY KEY (`id`),
 KEY `sender_id` (`sender_id`),
 KEY `recipient_id` (`recipient_id`),
 CONSTRAINT `mails_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `mails_ibfk_2` FOREIGN KEY (`recipient_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB

Необходимо выбрать отправителя (sender_id), получателя (recipient_id), последнее сообщение (message) и почитано ли это сообщение! (read)

Проблема в том, что когда я делаю группировку по полю

GROUP BY sender_id

чтоб получить уникальные "диалоги", MySQL ругается на неагрегированные столбцы
Ну а если отключить

sql_mode=only_full_group_by

я получаю вовсе не то, чего хотелось бы

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

помогите составить запрос. Или помогите в принципе )).
то, что у меня получилось, явно не то, что мне нужно

слабонервным не смотреть

SELECT GROUP_CONCAT(`m`.`message` ORDER BY `m`.`id` DESC) AS `message`,
					`u`.`id`, `u`.`name`, `m`.`read`
				FROM `mails` AS `m`
				JOIN `users` AS `u`
					ON `m`.`sender_id` = `u`.`id`
				WHERE `m`.`recipient_id` = $user_id
				GROUP BY `sender_id`

Comments

[Артём]

Надо наверное выносить некоторые данные в другую табличку, но голова уже совсем не соображает :(

Answer 1

[Артём]

Ну так вроде должно работать??

SELECT * FROM `mails` 
WHERE `id` IN (SELECT MAX(`id`) 
		FROM `mails` 
		GROUP BY `sender_id`) 
AND `recipient_id`= 1

Answer 2

[Максим]

SELECT distinct
first_value(`m`.`message`) over (partition by sender_id, recipient_id ORDER BY `m`.`id` DESC) AS `message`,
          `u`.`id`, `u`.`name`, `m`.`read` , recipient_id /*вывел чтоб было видно с кем он общался*/
        FROM `mails` AS `m`
        JOIN `users` AS `u`
          ON `m`.`sender_id` = `u`.`id`
        WHERE `m`.`recipient_id` = $user_id

Если надо реально последнее сообщение этого человека, уберите recipient_id
ORDER BY `m`.`id` DESC - так себе сортировка, лучше перейти на updated