Как получить общее количество не прочитанных сообщений для диалога?

Question

[xXRustamXx]

Схема диалогов

Я написал sql запрос:

SELECT du.* FROM (
    SELECT
        DISTINCT ON(du."dialogId") du."dialogId", 
        SUBSTRING(dm."message", 1, 60), 
        du."joinedDateTime",
        users."avatarUrl",
        users.username
    FROM dialogs_users du
    LEFT JOIN dialogs_messages dm ON dm."dialogId" = du."dialogId"
    LEFT JOIN users on users.id = dm."senderUserId"
    WHERE 
        du."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        AND
        dm.id NOT IN (
            SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
            WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        )
    ORDER BY du."dialogId", dm."message" DESC
) du
ORDER BY du."joinedDateTime" DESC;

который полностью удовлетворял, но появилась необходимость отображать в диалогах количество непрочитанных сообщений и тут я встрял... я пробовал делать Inner join с таблицей dialogs_messages, но тогда вышеописанный DISTINCT ON сходит на нет. Подскажите пожалуйста - как можно решить эту задачу?

Comments

[alexalexes]

Что из себя представляет таблица dialogs_messages?

Answer 1

[alexalexes]

У вас в итоговой выборке есть dialogId. С помощью него делаете подзапрос в select-e, чтобы получить единственное значение.

SELECT du.*, (
    SELECT COUNT(dm."receiverRead") "unreadMessages"
    FROM dialogs_messages dm
    WHERE 
        dm."dialogId" = du."dialogId" 
        AND dm."receiverRead" = false
        AND dm."senderUserId" NOT IN ('69e56a68-edbd-4f8b-8ccd-cb8031c5c865')
        AND dm.id NOT IN (
            SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
            WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        )
    GROUP BY dm."receiverRead"
) 
FROM (
    SELECT
        DISTINCT ON (du."dialogId") du."dialogId", 
        SUBSTRING(dm."message", 1, 60), 
        du."joinedDateTime",
        users."avatarUrl",
        users.username
    FROM dialogs_users du
    LEFT JOIN dialogs_messages dm ON dm."dialogId" = du."dialogId"
    LEFT JOIN users on users.id = dm."senderUserId"
    WHERE 
        du."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        AND dm.id NOT IN (
            SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
            WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        )
    ORDER BY du."dialogId", dm."message" DESC
) du
ORDER BY du."joinedDateTime" DESC;

Comments

[xXRustamXx]

Спасибо за очень сильную подсказку, я не знал, что можно использовать subquery в SELECT

[xXRustamXx]

Отредактируй свой ответ на:

SELECT du.*, (
    SELECT COUNT(dm."receiverRead") "unreadMessages"
    FROM dialogs_messages dm
    WHERE 
        dm."dialogId" = du."dialogId" 
        AND dm."receiverRead" = false
        AND dm."senderUserId" NOT IN ('69e56a68-edbd-4f8b-8ccd-cb8031c5c865')
        AND dm.id NOT IN (
            SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
            WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        )
    GROUP BY dm."receiverRead"
) FROM (
    SELECT
        DISTINCT ON (du."dialogId") du."dialogId", 
        SUBSTRING(dm."message", 1, 60), 
        du."joinedDateTime",
        users."avatarUrl",
        users.username
    FROM dialogs_users du
    LEFT JOIN dialogs_messages dm ON dm."dialogId" = du."dialogId"
    LEFT JOIN users on users.id = dm."senderUserId"
    WHERE 
        du."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        AND dm.id NOT IN (
            SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
            WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
        )
    ORDER BY du."dialogId", dm."message" DESC
) du
ORDER BY du."joinedDateTime" DESC;

И я отмечу ответ решением.

[alexalexes]

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