Mysql count with GROUP_CONCAT while using join query

Question

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][1]][1]

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

Thank you

I would look up conditional aggregation. Assuming you have a small number of reaction types, this should be a good solution. — Isolated
– Isolated, Commented Sep 8, 2022 at 18:03
Do you need the output in one column (1 like 2 loves) or rather as individual columns. The conditional aggregation works great for individual columns. — Isolated
– Isolated, Commented Sep 8, 2022 at 18:11

Hogan · Accepted Answer · 2022-09-08 18:19:00Z

3

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;

edited Sep 8, 2022 at 18:19

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

answered Sep 8, 2022 at 18:16

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Hkm Sadek Over a year ago

Getting error

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db.c.id'; this is incompatible with sql_mode=only_full_group_by

Hogan Over a year ago

@HkmSadek -- see the up date -- the out select needed the group by statement.

Hkm Sadek Over a year ago

Thanks it works. What do you think about this approach? Is there any really better ways to do unlimited types of reactions support?

Thorsten Kettner Over a year ago

@Hogan: Thank you. I had group by c.id and then removed it by mistake. (The group by on the id alone should suffice as the msg is functionally dependent on the primary key column.)

Thorsten Kettner Over a year ago

Unlimited? If the messages string gets too long, it will be truncated. You can alter the length limit, however. See dev.mysql.com/doc/refman/8.0/en/….

|

Collectives™ on Stack Overflow

Mysql count with GROUP_CONCAT while using join query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related