I am trying to generate some analytics about which reactions are used and which I can prune. There doesn't seem to be a way to do this in the admin panel, so I was going to see where it was stored in the database and count the usage that way.

Basically I am trying to see how many total times each individual reaction was used. So, where in the database does it store the reputation given and its type when someone reacts to a post?

It goes all to the same counter, "community reputation". Some add, some rest, and some don't do anything.

Nevermind, I see now what you meant. I'm not sure how to do that but I'm looking into it.

4 minutes ago, -FP said:

It goes all to the same counter, "community reputation". Some add, some rest, and some don't do anything.

Nevermind, I see now what you meant. I'm not sure how to do that but I'm looking into it.

Awesome, please let me know what you find. I am digging as well.

core_reputation_index - the "reaction" column stores the ID of the reaction used.

2 minutes ago, Ryan Ashbrook said:

core_reputation_index - the "reaction" column stores the ID of the reaction used.

Thank you so much.

Nice so you could run this in the ACP SQL Toolbox to see which one is most used.

SELECT reaction, COUNT(reaction) as Used
FROM core_reputation_index
GROUP BY reaction
ORDER BY COUNT(reaction) DESC

 

Also, for people who might read this and have come from way back and through many upgrades, mine was in x_utf_ipb_core_reputation_index. I assume it's because I went through some upgrades and unicode transformations, but someone correct me if I am wrong on that.

28 minutes ago, -FP said:

Nice so you could run this in the ACP SQL Toolbox to see which one is most used.

SELECT reaction, COUNT(reaction) as Used
FROM core_reputation_index
GROUP BY reaction
ORDER BY COUNT(reaction) DESC

 

That's great!  Can you add the hover name to that?  i.e. Like, Sad, Confused...

@Square Wheels this is the best I got now, I think it's correct but test it see what you get. (I don't know how to get to the reaction name itself so it lists the reaction icon name, hopefully your files have meaningful names that allow you to identify the reaction easily)

SELECT b.reaction_id, b.reaction_icon,
COUNT(a.reaction) AS times_used
FROM core_reputation_index AS a
JOIN core_reactions AS b 
ON a.reaction = b.reaction_id
GROUP BY b.reaction_id, b.reaction_icon, a.reaction
ORDER BY COUNT(a.reaction) DESC

 

15 hours ago, -FP said:

@Square Wheels this is the best I got now, I think it's correct but test it see what you get. (I don't know how to get to the reaction name itself so it lists the reaction icon name, hopefully your files have meaningful names that allow you to identify the reaction easily)

SELECT b.reaction_id, b.reaction_icon,
COUNT(a.reaction) AS times_used
FROM core_reputation_index AS a
JOIN core_reactions AS b 
ON a.reaction = b.reaction_id
GROUP BY b.reaction_id, b.reaction_icon, a.reaction
ORDER BY COUNT(a.reaction) DESC

 

I'm not a query guy, but I have something similar somewhere else, and the phrases are located in core_sys_lang_words.

INNER JOIN
core_sys_lang_words
ON
CONCAT('word_key_', core_reactions.reaction_id) = core_sys_lang_words.word_key

I use this as part of the query, maybe it could be a starting point if you were interested in trying to add the titles.