LINUX.ORG.RU

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

 ,


0

1

Дано: таблица chat_messages:

    Column    |           Type           | Collation | Nullable |                   Default                    
--------------+--------------------------+-----------+----------+----------------------------------------------
 id           | integer                  |           | not null | nextval('chat_chatmessage_id_seq'::regclass)
 created_at   | timestamp with time zone |           | not null | 
 updated_at   | timestamp with time zone |           | not null | 
 message      | character varying(4095)  |           |          | 
 recipient_id | integer                  |           | not null | 
 sender_id    | integer                  |           | not null |

Путем экспериментов я что-то типа такого получил:

# select * from chat_messages where id in (select max(id) from chat_messages group by least(sender_id, recipient_id), greatest(sender_id, recipient_id));
 id |          created_at           |          updated_at          |  message   | recipient_id | sender_id 
----+-------------------------------+------------------------------+------------+--------------+-----------
 21 | 2021-04-13 01:52:28.311547+00 | 2021-04-13 01:52:28.31161+00 | Это ты кал |           50 |        46
(1 row)

Но есть ли более красивое решение?

★★

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

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

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 2)
Ответ на: комментарий от crutch_master

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

    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)>.

tz4678 ★★
() автор топика
Ответ на: комментарий от tz4678
    queryset = ChatMessage.objects.extra(
        where=[
            "id in (select max(id) from chat_messages group by least(sender_id, recipient_id), greatest(sender_id, recipient_id))"
        ]
    )
Get All User Chats SQL: SELECT "chat_messages"."id", "chat_messages"."created_at", "chat_messages"."updated_at", "chat_messages"."sender_id", "chat_messages"."recipient_id", "chat_messages"."message" FROM "chat_messages" WHERE ((id in (select max(id) from chat_messages group by least(sender_id, recipient_id), greatest(sender_id, recipient_id))) AND ("chat_messages"."sender_id" = 46 OR "chat_messages"."recipient_id" = 46))
tz4678 ★★
() автор топика

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

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

no-such-file ★★★★★
()
Последнее исправление: no-such-file (всего исправлений: 1)
Ответ на: комментарий от tz4678

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

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 5)
Ответ на: комментарий от tz4678

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

crutch_master ★★★★★
()

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

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 2)
Ответ на: комментарий от crutch_master

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

deep-purple ★★★★★
()
Ответ на: комментарий от tz4678

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

fakeroot
()
Ответ на: комментарий от fakeroot

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


    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 хуже Алхимии.

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

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

tz4678 ★★
() автор топика
Ответ на: комментарий от crutch_master

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

tz4678 ★★
() автор топика

Можно попробовать через 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-ом. Ну или поудобнее структуру БД придумал.

theNamelessOne ★★★★★
()
Последнее исправление: theNamelessOne (всего исправлений: 2)

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

anonymous
()
Ответ на: комментарий от theNamelessOne

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

tz4678 ★★
() автор топика
Ответ на: комментарий от tz4678

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;

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

level1 ★★
()
Последнее исправление: level1 (всего исправлений: 2)
Ответ на: комментарий от level1

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

tz4678 ★★
() автор топика
Ответ на: комментарий от tz4678

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

t184256 ★★★★★
()
Ответ на: комментарий от tz4678

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

fakeroot
()

Лучше не ленитья и объеденить запросы по 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
    )
)

Psilocybe ★★★★
()
Ответ на: комментарий от Psilocybe

Извини, запрос неправильно написал. Идея в том , чтобы явно разделить случаи когда 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)

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

Psilocybe ★★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.