MySQL — выборка диалогов с тремя последними сообщениями?

Question

[PobyKorn]

Добрый день!

Есть две таблицы:

--- dialogs
- id
- date_latest_message

--- dialog_messages
- id
- dialog_id
- message
- added_date

Нужно сделать выборку диалогов по date_latest_message и подгрузить 3 последних сообщения из каждого диалога.
Что-то вроде этого:

SELECT 
	   D.dialog_id, 
       D.date_latest_message,
	   DM.message,
	   DM.added_date AS message_added_date
FROM (
	SELECT 
	       d.id AS dialog_id, 
	       d.date_latest_message
	FROM dialogs  d
	WHERE DATE(d.date_latest_message) BETWEEN '2020-10-01' AND '2021-10-08'
) D LEFT JOIN (
	SELECT 
	       dm.message,
	       dm.dialog_id,
	       dm.id,
	       dm.added_date
	FROM dialog_messages dm
	WHERE 1
	ORDER BY dm.id DESC
	LIMIT 3
) DM ON DM.dialog_id=D.dialog_id

WHERE 1
ORDER BY D.date_latest_message DESC

Запрос возвращает messages только для одного диалога, всё остальное NULL.
Как сделать правильно?

Comments

[Рональд Макдональд]

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

Оформить код не в blockquote, а в code.

[PobyKorn]

исправил

[Akina]

Мало исправил. MARIADB и MYSQL уже давно не одно и то же. Так что надо не только оставить одну из двух DBMS, но и ещё указать точную версию.

[ThunderCat]

я извиняюсь, а на зачем вообще такая сложная таблица как --- dialogs? Есть какой-то сакральный смысл в денормализованной таблице с абсолютно бесполезным содержимым?

[alexalexes]

ThunderCat, а как еще сообщения по разным чатам раскидать?
Сущность диалог (он же чат в широком смысле слова) есть, ее нужно выделить.

Answer 1

[alexalexes]

Вариант для MySQL ниже 8 версии:

select A.*
from 
(
SELECT d.id AS dialog_id, 
       d.date_latest_message,
       (select max(DM.added_date)
           from dialog_messages dm3
          where dm3.dialog_id = dm.dialog_id
       ) as datetime_latest_message,
       Dm.Id as Dm_Id,
       DM.message,
       DM.added_date
  FROM dialogs d
  join dialog_messages dm on DM.dialog_id = D.dialog_id
  WHERE (select count(*)
           from dialog_messages dm2
          where dm2.dialog_id = dm.dialog_id
          and dm2.id > dm.id -- по идент. будет эффективнее работать, чем по дате-время, к тому же первичный ключ, как правило, проиндексирован, не требуется доп. индексов
        ) < 3 -- берем только те сообщения, которые позднее опубликованы чем текущее (3 вышестоящих)
    and DATE(d.date_latest_message BETWEEN) '2020-10-01' AND '2021-10-08'
) A
order by A.datetime_latest_message desc, A.Dm_Id desc

Если MySQL версии 8 и выше, как вариант, можно взять оконную функцию row_number.

select A.*
from
(
  SELECT 
         d.id AS dialog_id, 
         d.date_latest_message,
         (select max(DM.added_date)
           from dialog_messages dm3
          where dm3.dialog_id = dm.dialog_id
         ) as datetime_latest_message,
         Dm.Id as Dm_Id,
         DM.message,
         DM.added_date AS message_added_date,
         row_number() over (partition by d.id              -- окно счетчика в пределах идент. диалога
                            order by DM.Id desc    -- направление сортировки счетчика
                           ) dm_rownum -- счетчик для отсечения порций
  FROM dialogs d
  join dialog_messages dm on DM.dialog_id = D.dialog_id
  WHERE DATE(d.date_latest_message) BETWEEN '2020-10-01' AND '2021-10-08'
) A
where A.dm_rownum <= 3 -- отсекаем нужное число "локальных" отсчетов
ORDER BY A.datetime_latest_message DESC, A.Dm_Id DESC

Comments

[Akina]

where A.dm_rank <= 3

Ты же понимаешь, что CTE может вернуть десяток записей с dm_rank = 1? тут нужно применять ROW_NUMBER() и расширять сортировку до обеспечения уникальности записи.

Это даже если не нудеть о том, что вот никакое ограничение в структуре не мешает d.date_latest_message не соответствовать максимальному DM.added_date.

в младших версиях придется делать пользовательские переменные через @, чтобы сделать функционал rank-а.

Да ладно! легко эмулируется на коррелированном группирующем подзапросе. Другое дело производительность - работа с переменными всегда однопроходный фуллскан, а группировка может и индексы подтянуть, так что вопрос, какой вариант производительнее, упирается в среднее количество записей на группу.

[alexalexes]

Ты же понимаешь, что CTE может вернуть десяток записей с dm_rank = 1? тут нужно применять ROW_NUMBER() и расширять сортировку до обеспечения уникальности записи.

Да, нужно заглянуть в документацию, чем отличаются оконки-счетчики. Я, обычно, по ситуации вставляю rank/row_number/dense_rank. На эксперименте сразу видно, что больше подходит. Переписал сравнения и сортировки по датам на работу с id сообщения. Идентификатор быстрее обработать, чем дату, к тому же к ключам может быть индекс, просто по тому, что ключу необходим индекс.

Это даже если не нудеть о том, что вот никакое ограничение в структуре не мешает d.date_latest_message не соответствовать максимальному DM.added_date.

Ну, пускай будет максимум по дате-время сообщения диалога.

Да ладно! легко эмулируется на коррелированном группирующем подзапросе.

Ок. Я считал, что такие финты с подзапросами - это от безысходности, если есть возможность использовать более приспособленные инструменты, то почему бы и нет. Привел пример, с подзапросом.

[PobyKorn]

alexalexes, благодарю! то что нужно

Answer 2

[Антон Антон]

Может проще в сообщение добавить признак того, что оно "в тройке последних"? Ну или в табличке рядом хранить идентификаторы чатов и последних сообщений? И при добавлении нового сообщения обновлять данные? Тогда запрос упрощается до безобразия + учитывая, что обновление намного реже вставки - с производительностью всё будет намнооого лучше.