Mysql count with GROUP_CONCAT while using join query-CodePudding

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.

How can I add numbers with reaction or there are better and performant options I have? Please suggest.

Thank you

CodePudding user response：

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;