I have a chats table and chat_reactions table. Each chat message can have many reactions and a reaction is a text and it can be many. I am trying to return messages with the grouped reactions and the total number of times a particular reaction is used. For example,
msg: hi with id 1 got a total of three reactions.
1 LIKE AND 2 LOVES. How can I return it?
Here is the query I am trying
SELECT c.id, c.msg, GROUP_CONCAT(cr.reaction) as reaction FROM chats as c LEFT JOIN chat_reactions as cr on c.id = cr.chat_id GROUP BY c.id
My result looks like this.
[![enter image description here]]
How can I add numbers with reaction or there are better and performant options I have? Please suggest.
First count the post reactions. Then aggregate per post.
select c.id, c.msg, group_concat(concat(cr.reaction, '(', cnt, ')') order by cr.reaction separator ', ') as reactions from chats as c left join ( select chat_id, reaction, count(*) as cnt from chat_reactions group by chat_id, reaction ) cr on cr.chat_id = c.id group by c.id, c.msg order by c.id;