Select last conversations by counting the unread messages in each conversation

0

I made a chat in NodeJS and I can already bring the list of conversations. However, I also need to bring the amount of unread messages from each conversation.

Currently this query brings the total number of unread messages in all conversations.

SELECT SQL_CALC_FOUND_ROWS
            u.id AS uid,
            ch.sender_id,
            ch.notified,
            ch.receiver_id,
            u.name AS name,
            u.surname AS surname,
            ch.created_at,
            ch.message AS message,
            ch2.unread
        FROM chats ch
        NATURAL JOIN (
            SELECT
                SUM(IF('read' = 0 , 1,0)) AS unread
            FROM     chats
            WHERE    receiver_id = 1
        ) ch2
        INNER JOIN users u ON u.id = 
            IF(ch.sender_id = 1,
               ch.receiver_id,
               ch.sender_id
            )
            WHERE ch.id IN(
                SELECT MAX(id) AS id FROM(
                    SELECT id,
                        sender_id AS id_with
                    FROM chats
                    WHERE receiver_id = 1
                    UNION ALL
                    SELECT id,
                        receiver_id AS id_with
                    FROM chats
                WHERE sender_id = 1) AS t
                GROUP BY id_with)
                ORDER BY ch.id DESC
    
asked by anonymous 14.10.2016 / 05:27

0 answers