Query of replies made by staff includes actions like opening and closing threads

Product Support

AI-generated summary

In a support discussion titled ‘Query of replies made by staff includes actions like opening and closing threads,’ jerdog is building SQL queries to track staff replies within a date range but notices the results include non-reply activities like topic moderation. vinothkannans advises filtering by p.post_type = 1 to isolate regular posts, noting other types: moderator_action (2), small_action (3), and whisper (4). jerdog thanks them, then asks for clarification on what qualifies as a ‘small_action.’ Falco provides a visual example of such actions (e.g., system messages or minor updates), and jerdog confirms understanding. The solution effectively excludes moderation and meta-actions, focusing only on actual staff replies.

1

I am building queries for my community to be able to fulfill some metrics, and what I am seeing is something I can’t seem to get around. The following query essentially just pulls back all posts made by staff members during a time period, but also includes activities like opening/closing a topic, etc.

-- [params]
-- string :start = 2018-03-04 00:00:00
-- string :end = 2018-03-10 23:59:59.999999

SELECT p.user_id, count(p.id) as Replies
    FROM posts p
    WHERE p.created_at between :start and :end AND
        p.user_id IN 
        (select u.id 
            from users u
            where u.primary_group_id = 41) AND
        p.user_id not in ('-1', '-2') AND
        p.topic_id not in ('4','24','26','32','33','34','35','36','37','38','39','40')
GROUP BY p.user_id
ORDER BY Replies DESC

I see a post_action_types table but it has things like Likes, Bookmarks, etc.. I am trying to get to where I am displaying actual replies by my staff and not just things like “likes” and such

2

You should filter only regular posts by WHERE p.post_type = 1.

Available post types are regular: 1, moderator_action: 2, small_action: 3, whisper: 4.

3

Brilliant. Thanks mate.

4

What would classify as a “small_action”?

5

Stuff like this:

image
image1162×115 6.56 KB

6

Perfect -thanks for the clarification