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

user_id
sender_id
text
isread
date_create

Максимально просто и все хорошо работает, но ест одна проблема. Помимо текста юзеру надо показать панельку с чатами в которых он принимает участие с другими пользователями. И отобразить их надо в хронологическом порядке (самый свежий чат вверху) и конечно показать сколько в этом чате непрочитанных. Как это сделать одним запросом?

С одной стороны - нужна сортировка по убыванию даты создания сообщения, с другой стороны - группировка по id юзера (или user_id или sender_id). Какое есть хорошее решение, в плане что запрос будет достаточно быстрым для большого количества записей в таблице?

Проект разрабатывается, поэтому поменять структуру - нет проблем. Единственное что не хотелось бы - уходить от одной таблицы.

Dimitry Sibiryakov, А сколько надо? Мне кажется это можно решить одним запросом, но пока я не понял как, допускаю что может я ошибаюсь и одним никак, собственно это и хотел узнать.

miksoft, Любой из, просто в зависимости от роли текущего пользователя - будет суммирование непрочитанных меняться (т.е. мне не нужно знать сколько непрочитанных сообщений которые отправил я, но нужно знать сколько новых отправили мне).

Akina, 5.7

user_id - получатель сообщения
sender_id - отправитель

у чата = группы сообщений должен быть единый для них идентификатор. Если оставаться в рамках одной таблицы, то первое сообщение должно получать идентификатор чата (например, можно использовать числовое представление даты-времени на сервере, вычислять порядковый номер запросом, прикрутить UUID), а далее все последующие сообщения ветки в момент инсерта записи получают один и тот же идентификатор, присвоенный первому сообщению. Отбор сообщений осуществляется группировкой по идентификатору чата.

Да, я думал над этим, т.к. два поля user_id создают сложности. Пожалуй так и сделаю, спасибо.

Я видимо плохо объяснил, но задача только на вывод списка чатов: дальше юзер кликает по одному из юзеров - и отдельным запросом получаем список всех сообщений. Одним запросом имелось ввиду получить список чатов выстроенных в убывающем порядке с подсчитанным числом непрочитанных сообщений

Спасибо, вроде то что нужно в итоге.
Финальный запрос получился такой:




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

Именно потому что нет - и буду игнорить. Результат - массив IDs собеседников, т.е. или текущий юзер начал беседу или его собеседник, но в итоге - мне нужно показать список ТОЛЬКО собеседников, т.к. чата с самим собой нет. А по клику на пункт из списка подгружается переписка.