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