MySQL - fetching conversations with the last 3 messages?

Question

MySQL

Good afternoon!

There are two tables:

--- dialogs
- id
- date_latest_message

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

You need to make a selection of dialogs by date_latest_message and load the last 3 messages from each dialog.
Something like that:

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

The request returns messages for only one dialog, everything else is NULL.
How to do it right?

Answer 1

Option for MySQL below version 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

If MySQL is version 8 or higher, you can use the row_number window function as an option .

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

Answer 2

Maybe it's easier to add a sign to the message that it is "in the last three"? Well, or in the plate next to store the identifiers of chats and recent messages? And when adding a new message, update the data? Then the query is simplified to disgrace + given that the update is much less frequent than the insert - everything will be much better with performance.