Блог

Я хочу добавить мессенджер в свой любимый проект, но у меня возникают трудности с написанием запросов к базе данных. Я использую MySQL для этой службы с гибернацией в качестве ORM. Почти все запросы были написаны на HQL, но в принципе я могу использовать собственные запросы.

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

Ниже я описал упрощенную структуру двух таблиц, важных для этой задачи.

Таблица сообщений:

Таблица Member_event:

 where type:
1 - user entered the chat,
2 - user leaved the chat,
3 - user cleared his own history of messages in the chat
 

Возможно ли прочитать все сообщения чата, доступные пользователю, одним запросом?

Я понятия не имею, как динамически проверять условия: ГДЕ временные метки сообщения находятся между всеми циклами «введено-оставлено» и после последнего «введено», если не оставлено, НО только после последней очистки истории. Если существует.

Я думаю, вы могли бы выполнить следующие шаги:

1. возьмите объединение обеих таблиц и рассмотрите записи в порядке временной метки
2. Используйте оконные функции, чтобы определить, был ли самый последний тип 1 или 2 равен 1. Мы можем использовать текущую сумму, где тип 1 добавляет единицу, а тип 2 вычитает единицу (а 3 ничего с этим не делает). С помощью другой оконной функции вы можете определить, существует ли еще следующий тип 3. Комбинация этих двух сведений может быть преобразована в 1, когда строка принадлежит интервалу, который должен быть собран, и в 0, когда нет.
3. Отфильтруйте предыдущий результат, чтобы получить только записи сообщений, и только те, где вычисление составило 1.

Вот запрос:

 with unified as (
    select   id, text, timestamp, null as type
    from     message
    union
    select   id, null, timestamp, type 
    from     member_event
    where    user_id = 1),

validated as (
    select   unified.*,
             sum(case type when 1 then 1 when 2 then -1 else 0 end) 
                over (order by timestamp
                      rows unbounded preceding) * 
             min(case type when 3 then 0 else 1 end) 
                over (order by timestamp
                      rows between current row and unbounded following) valid
    from     unified
    order by timestamp)

select   id, text, timestamp
from     validated
where    type is null and valid = 1
order by timestamp
 

Я не понимаю, как вы могли бы сопоставить таблицу Member_event с таблицей Message_table без дополнительного FOREIGN_KEY . Вы пытаетесь назначить сообщения, доступные пользователю, с помощью метки времени? Если это так, попробуйте следующее:

 SELECT * FROM MESSAGE_TABLE m
WHERE m.TIMESTAMP BETWEEN 
(SELECT TOP 1 TIMESTAMP FROM MEMBER_EVENT_TABLE WHERE type = 1 ORDER BY TIMESTAMP DESC) 
AND (SELECT TOP 1 TIMESTAMP FROM MEMBER_EVENT_TABLE WHERE type != 1 ORDER BY TIMESTAMP DESC)
 

Это, по крайней мере, должно показывать последние сообщения между join и clean / leave