Select last conversations by counting unread messages in each conversation

Asked

Viewed 58 times

0

I have done 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
  • And what’s wrong with putting another clause in the WHERE?

  • The question is, where to put?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.