Как правильно написать запрос на получение всех диалогов?

Как правильно написать запрос на получение всех диалогов?
Путем экспериментов я что-то типа такого получил:

Но это не диалог. Это какое-то последнее сообщение из переписки. «Диалог» у тебя в бд отсутствует как сущность.
Что за СУБД, кстати?

postgres, но диалог по-сути это последнее сообщение

А самое главное не знаю как эту хрень записать в ублюдочной джанге

    queryset = ChatMessage.objects.filter(
        id__in=ChatMessage.objects.raw(
            "select max(id) as id from chat_messages group by least(sender_id, recipient_id), greatest(sender_id, recipient_id)"
        )
    )

TypeError: Field ‘id’ expected a number but got <ChatMessage: ChatMessage object (21)>.

но диалог по-сути это последнее сообщение

Последнее сообщение - это последнее сообщение, а диалог - это диалог!

открой телеграм у тебя там список последних сообщений для каждого собеседника

более красивое решение

Сохраняй последний id в отдельной таблице. И/или сделай вьюху только с последними сообщениями.

Если хочешь диалоги, то тебе надо добавлять такую сущность. И в messsages уже будет не recipient_id и sender_id, а dialog_id, owner_id + таблички dialog (id, caption, etc), dialog_users (id_dialog, id_user) и у тебя реализуются так не только диалоги, но и чат-румы. Потом также выбираешь max(id) с группировкой по ид диалога без плясок с сортировкой ид отправителя/получателя. А так у тебя messages для частного случая, диалогов не подразумевает и весь функционал будет добавляться исключительно костылями.

мне не нужны групповые чаты, поэтому мне одной таблицы хватит

У тебя уже задача вылезла за рамки одной таблицы и ты пляшешь с перестановками полей в группировке. Завтра ты захочешь сделать x и придётся делать еще более нелепые манёвры.

И вообще у тебя аномалия удаления, когда получатель выпилится вся исходящая почта пойдёт по бороде.

не мешай ему собственные ноги отстреливать — видишь, он не использует пхп с мускулем, дело точно не в бобине.

Для постгриса посмотри Grouping Sets.

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

на алхимии таких костылей писать не надо:

    ChatMessage.objects.extra(
        where=[
            "id in (select max(id) from chat_messages group by least(sender_id, recipient_id), greatest(sender_id, recipient_id))"
        ]
    )

Отсюда вывод: Django ORM хуже Алхимии.

Напиши мне этот кусок на джанге.

Хороший совет - это не плодить сущности без необходимости.

почитал. мимо

если получатель выпилится, то удалятся все его исходящие и входящие сообщения, просто исчезнут у всех диалоги с ним

Можно попробовать через DISTINCT ON:

   select distinct on(least(sender_id, recipient_id),
                      greatest(sender_id, recipient_id))
          *
     from chat_messages
 order by least(sender_id, recipient_id)
        , greatest(sender_id, recipient_id)
        , id desc;

Можно ещё вместо id desc использовать created_at desc (если индекс есть).

Но я бы просто последние сообщения в отдельную таблицу клал upsert-ом. Ну или поудобнее структуру БД придумал.

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

в джанге такой запрос не получится написать

Если верить вот этому тикету, то уже лет 9 как получится.

Выбери max(id) отдельным запросом, потом полученные ID вставь в id__in.

это не по функции. там нужно annotate + distinct. лень разбираться, гуглить по 3 часа, если уж в документации нет примеров нормальных

grouping sets поможет тебе в одном запросе совместить отправителя и получателя. Свежесть найдешь оконной функцией. И будут два подзапроса: с grouping sets, и вторая с оконной.

WITH max_upd AS (SELECT ... max(updated_at)... GROUP BY grouping sets ...),
     lasts AS (SELECT ... row_number OVER (PARTITION BY...) n
         FROM max_upd ...
SELECT ... FROM lasts WHERE n = 1;

И если тебе нужны полные данные по сообщению, то будет селф-джоин.

согласись, не намного лучше моего решения (отмечу, что оно полностью рабочее), у господина с путиным на аве решение лучше моего, но оно мне не подходит потому как я не знаю как такой запрос с помощью Django ORM написать

Слева список диалогов, который формируется тем запросом.

image

Это не повод называть последнее сообщение «диалогом».

хватит стрелять себе в ногу. если так не хочется добавлять еще одну таблицу, то почему просто не помечать последнее сообщение в диалоге? например: last_message = true? это избавит тебя и от новых таблиц и от кривых запросов…

Лучше не ленитья и объеденить запросы по union all чем пихать функции в group by еще может помочь построение индекса по ключу группировки

как то так

select * from chat_messages where id in (
    select max(id) from ( 
        select max(id) 
        from chat_messages 
        group by sender_id, recipient_id
        union all
        select max(id) 
        from chat_messages 
        group by recipient_id,sender_id
    )
)

Извини, запрос неправильно написал. Идея в том , чтобы явно разделить случаи когда sender и recipient меняются местами, но написал неверно впопыхах. Вот так будет правильно

select * from chat_messages where id in (
    select max(id) from ( 
        select sender_id id1, recipient_id id2, max(id) id
        from chat_messages 
        group by sender_id, recipient_id
        union all
        select recipient_id id1,sender_id id2 max(id) id
        from chat_messages 
        group by recipient_id,sender_id
    )
    group by id1,id2
)

Такие запросы могут работать быстрее (как ни странно это), чем более сложный запрос в заголовке из-за отсутствия функций в группировке. Но надо пробовать.

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

least(sender_id, recipient_id), greatest(sender_id, recipient_id)

и тогда планировщик возможно более эффективный план запроса построит