So I’m trying to write a SQL query that will give someone a badge if they mention 2 or more people in a SINGLE post.
I know that ‘action_type 7’ is a mention (discourse/user_action.rb at master · discourse/discourse · GitHub), and that the ‘acting_user_id’ is the user that is creating the mentions.
I know that I have 15 mentions in my forum by querying ActiveRecord with ‘UserAction.where(action_type: 7)’.
My current SQL is as follows:
SELECT users.id user_id, posts.id as post_id, COUNT(user_actions.target_post_id) FROM users JOIN posts ON (posts.user_id = users.id) JOIN user_actions ON (user_actions.acting_user_id = users.id) WHERE user_actions.target_post_id IN (SELECT target_post_id FROM user_actions WHERE action_type = 7) GROUP BY users.id, posts.id HAVING COUNT(user_actions.target_post_id) > 2
If anyone could provide enlightenment, it would be appreciated.