Which reactions are the most commonly-used in your community?

[jericson (Jon Ericson)]

Continuing the discussion from Reactions on Meta:

I was curious about how reactions are used on a site I work with part-time. It’s a forum about college admissions, so there are posts about students being accepted/rejected from their dream school and that sort of thing. We use as our “like” emoji, so that’s why it ranks so high compared to , which is the default “like”. Here’s what I found:

reaction	posts	count
	260494	720859
	16116	25738
	13315	23523
	7538	22674
	7200	15720
	6341	26875
	3854	4404
	2795	4911
	2286	2881
	1758	2623
	1708	2360
	1613	3396
	336	352
	30	43

I created the initial list of reactions by looking at which are the most commonly-used emojis in posts. We adjusted the list based on member feedback. I’m a fan of but it was clear very early on that the community wasn’t. So I swapped it for something else.

If you want to see how your site compares, the query I used is:

select ':'||reaction_value||':' reaction, 
       count(*) posts, 
       coalesce(sum(reaction_users_count), sum(like_count)) count
from discourse_reactions_reactions drr
     join posts p on post_id = p.id
group by reaction_value
order by count(*) desc

Fun fact: we struggled to figure out why the emoji was so commonly used in posts. Eventually I tracked down the culprit: .

[JammyDodger]

Let’s slide this over to community to get more eyes on it.

Though the query isn’t counting Likes properly, or at least not all of ours (possibly because we’ve turned Reactions on mid-flow)

reaction	posts	count
	52295	144915
	1167	1224
	1101	1236

versus:

reaction_value	count
	1371442
	1236
	1224

FWIW I've been using a small variation of this one to produce our table:

SELECT source.reaction_value,
       count
FROM

 (
 (
 
SELECT 
    CASE WHEN post_action_type_id = 2 THEN 'heart' END AS reaction_value,
    COUNT(*) AS count
FROM post_actions
WHERE post_action_type_id = 2
  AND deleted_at IS NULL
GROUP BY 1

)
UNION ALL
(

SELECT 
    reaction_value,
    SUM(reaction_users_count) AS count
FROM discourse_reactions_reactions
WHERE reaction_value <> 'heart'
GROUP BY 1

)
) AS source

GROUP BY 1,2
ORDER BY 2 DESC

There’s also the stock one in the reports section too to cross-check (but the table isn’t as easy to copy and paste ) - /admin/reports/reactions

[jericson (Jon Ericson)]

Ah. UNION ALL is the right way to do this query since you are counting two very different things. My query needs an outer join to cover posts that don’t have an entry in discourse_reactions_reactions. Adding that caused my query to time out, so I can’t verify that solves the problem. Union is the right answer here in any case.

I’m a bit curious if post_actions might overcount likes, though. Does it account for people liking a post and then removing or changing that reaction?

I needed to change ‘heart’ to ‘+1’ in the query since we used for our likes at College Confidential. Here’s my result from that query:

reaction	count
	2089798
	28167
	27070
	24676
	24055
	16778
	5325
	4590
	3547
	3078
	2623
	2443
	369
	43

[JammyDodger]

@jericson

I’m a bit curious if post_actions might overcount likes, though. Does it account for people liking a post and then removing or changing that reaction?

That’s a good point. Let me go back and slip a AND deleted_at IS NULL in.