I’m planning to replace retort with discourse reactions. To find out which emojis to use, I wanted to know how their usage in my forum is. As I couldn’t find a query for this I share mine:
Count emojis added with the retort plugin
SELECT count(*), REGEXP_MATCHES(key, '([a-z_+]+)|retort') emoji
WHERE extra = 'retort'
GROUP BY key
ORDER BY 1 DESC
Count emojis in posts (without the system user)
SELECT count(*), REGEXP_MATCHES(raw, ' :([a-z_]+): ', 'g') emoji
WHERE user_id > 0
GROUP BY emoji
ORDER BY 1 DESC
Nice. I’ve not used the
REGEXP_MATCHES before, but I’ve just given these a test drive (well, the second one. I don’t have Retort installed ).
I think you want to
GROUP BY emoji rather than
user_id > 1 would exclude the original admin. That may be intended, but just thought I’d mention it.
I also asked Bert for an explanation too if anyone else would find it useful:
What Bert said:
This query is used to count the number of times each emoji is used in the
Here’s a breakdown of the query:
SELECT count(*), REGEXP_MATCHES(raw, ' :([a-z_]+): ', 'g') emoji: This is the main part of the query. It’s counting the number of rows (
count(*)) and using a regular expression (
REGEXP_MATCHES) to match and extract the emoji from the
raw column. The regular expression
' :([a-z_]+): ' is looking for patterns that start and end with a colon (
:) and have one or more lowercase letters or underscores in between. The ‘g’ flag means it will match globally, i.e., find all matches rather than stopping after the first match. The matched emoji is then aliased as
FROM posts: This specifies the table from which the data is being retrieved, in this case, the
WHERE user_id > 0: This is a condition that filters the rows being considered. Only rows where the
user_id is greater than 0 are included.
GROUP BY emoji: This groups the results by the
emoji alias. This means that for each unique emoji, it will count the number of rows.
ORDER BY 1 DESC: This orders the results by the first column in the result set (which is the count) in descending order. This means the emojis used most frequently will appear first.
So, in summary, this query is counting the number of times each emoji is used in the
posts table, for posts where the
user_id is greater than 0, and ordering the results so that the most frequently used emojis appear first.
Oh, you are right. I changed it on my site but forgot here.
Indeed, I should have mentioned that in my post. user_id 1 is a legacy user (when anonymous posting was still allowed on my site) which I didn’t want to include in the count.