How to get the total number of unread messages for a conversation?

Question

SQL

Dialogue scheme

I wrote sql query:

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;

which completely satisfied, but there was a need to display the number of unread messages in the dialogs, and then I stuck ... I tried to do an Inner join with the dialogs_messages table, but then the above DISTINCT ON comes to naught. Please tell me - how can I solve this problem?

Answer 1

You have a dialogId in the final selection. With it, you make a subquery in select-e to get a single value.

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;