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

support
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.

8 Likes
3

Brilliant. Thanks mate.

2 Likes
4

What would classify as a “small_action”?

5

Stuff like this:

image
image.png1162×115 6.56 KB

6 Likes
6

Perfect -thanks for the clarification