SQL Query that occurs upon Mentions!


(Ivan) #1

Hi guys.

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.


(Ivan) #3

So I figured it out. I tried to “refresh” my memory from what I’d done and went back to basics. I first did ‘SELECT * FROM user_actions WHERE action_type = 7’, which aided me in solving this problem. Somehow visualising it in a slightly different manner made all the difference! In the end, the query I came up with was:

SELECT acting_user_id user_id, target_topic_id, target_post_id post_id, COUNT(target_post_id), topics.updated_at granted_at FROM user_actions 
JOIN posts ON (posts.id = user_actions.target_post_id)
JOIN topics ON (posts.topic_id = topics.id) 
WHERE action_type = 7
AND (:backfill OR posts.id IN (:post_ids))
GROUP BY target_topic_id, post_id, acting_user_id, topics.updated_at
HAVING COUNT(target_post_id) >= 2

To make it clear, this query grants a badge to each user that in a single post, has mentioned at least 2 users.