I am working on adding a plugin to my Discourse instance that determines whether a certain number of flags were recently made in a category. It would ideally return the count of flags given in a category over a time range (say number of flags in past 20 minutes for category with id X).
I am playing around with the Data Explorer plugin, but I’m not very familiar with SQL and am not sure if what I’m trying to do is possible. It seems like I could accomplish this on a per-post basis using the post_actions table as post_id is stored for post actions. Ideally I would accomplish this in one query instead of manually summing the flag counts for individual posts in a category.
I don’t have a very robust Discourse instance available where I can test this out thoroughly, but I think this Data Explorer query might get you going in the right direction. (Someone else can probably chime in to clean it up!)
SELECT count(*)
FROM post_actions AS pac
JOIN posts p on p.id = pac.post_id
JOIN topics t on t.id = p.topic_id
WHERE pac.post_action_type_id in ('3','4','7','8')
AND pac.created_at >= NOW() - INTERVAL '20 MINUTE'
AND t.category_id = '4'
In this example, I’ve specified the time since the flag was created to be 20 minutes (line 6), as that’s what you gave in your example.
In the 7th line, I used the category ID of 4, but you’ll need to use the specific category ID for the category you’re wanting to track.
If you want to be able to refer to the category by name, you could add another join to the query, so it would look like this:
SELECT count(*)
FROM post_actions AS pac
JOIN posts p on p.id = pac.post_id
JOIN topics t on t.id = p.topic_id
JOIN categories c on c.id = t.category_id
WHERE pac.post_action_type_id in ('3','4','7','8')
AND pac.created_at >= NOW() - INTERVAL '20 MINUTE'
AND c.name = 'General'
Then replace where I have used General in line 8 with the name of your category. Keep in mind the category names are case-sensitive.
If you are interested, I added custom fields you could fill in:
--[params]
-- int_list :category_ids
-- text :interval = 20min
SELECT t.category_id, count(pa.id) count
from post_actions pa
INNER JOIN posts p on p.id = pa.post_id
INNER JOIN topics t on t.id = p.topic_id
WHERE post_action_type_id in (3, 4, 6, 7, 8)
AND t.category_id in (:category_ids)
AND pa.created_at >= now() - interval :interval
GROUP BY t.category_id
You can enter multiple categories IDs and custom intervals (such as 1h, 30 min, 3 months, etc)
Note: I kept the post_action_type_id 6, which I believe is related to a user receiving a warning; I’m not sure if you want to count as well; feel free to remove it then.
