Какой запрос сделать к базе данных?

Question

[webymax]

В таблицу `messages` сохраняются сообщения между юзерами:
`from_user_id`
`to_user_id`
`message`
`datetime`

Один и тот же юзер может быть как отправителем, так и получателем. Соответственно, его id может быть как в поле `from_user_id`, так и в `to_user_id`.

Подскажите, пожалуйста, какой запрос нужно составить к базе, чтобы выбрать по одному последнему (по `datetime`) сообщению юзера с определённым ID (не важно отправитель он или получатель) с уникальными оппонентами.

К примеру, у Димы с ID=4 есть сообщения с другими юзерами. Нужно выбрать по одному последнему сообщению Димы с каждым юзером, в котором Дима или отправитель или получатель.

Answer 1

[SymphoGraph]

SELECT
    subquery.opponent_id,
    subquery.last_message_datetime,
    m.message AS last_message
FROM (
    SELECT
        CASE
            WHEN m.from_user_id = 4 THEN m.to_user_id
            ELSE m.from_user_id
        END AS opponent_id,
        MAX(m.datetime) AS last_message_datetime
    FROM messages AS m
    WHERE m.from_user_id = 4 OR m.to_user_id = 4
    GROUP BY opponent_id
) AS subquery
LEFT JOIN messages AS m ON (
    (m.from_user_id = 4 AND m.to_user_id = subquery.opponent_id)
    OR
    (m.to_user_id = 4 AND m.from_user_id = subquery.opponent_id)
) AND m.datetime = subquery.last_message_datetime

Comments

[webymax]

Спасибо! Пока разберёшь ваш запрос, многому научишься ) Только где-то какая-то неучтённость, потому что есть несостыковка сообщений и времени их добавления в базу

.

Вот вся таблица:

[SymphoGraph]

webymax, поправил. Вроде бы сходится.

[webymax]

Всё сходится теперь! Спасибо!!!

Answer 2

[Rsa97]

WITH
  `cte1` AS (
    SELECT `to_user_id` AS `respondent`, `message`, `datetime`,
           'outgoing' AS `direction`
      FROM `messages`
      WHERE `from_user_id` = :userId
    UNION ALL
    SELECT `from_user_id`, `message`, `datetime`,
           'incoming' AS `direction`
      FROM `messages`
      WHERE `to_user_id` = :userId
  ),
  `cte2` AS (
    SELECT `respondent`, `message`, `datetime`, `direction`,
           ROW_NUMBER() OVER `w` AS `row_num`
    FROM `cte1`
    WINDOW `w` AS (
      PARTITION BY `respondent`
      ORDER BY `datetime` DESC
    )
  )
  SELECT `respondent`, `message`, `datetime`, `direction`
    FROM `cte2`
    WHERE `row_num` = 1

Comments

[webymax]

Ну и запросище )) Верно выдаёт! Спасибо!!! Буду разбирать...

[webymax]

Походу базу данных нужно оптимизировать. Мне кажется не дело такими тяжёлыми запросами пользоваться )