XF 1.5 Query For Admins & Moderators Stats

[Brent W]

Hi,

I was wondering if anyone could help me out with a query that would provide me total posts over x time for current admins and moderators?

 

[Chris D]

SELECT user.user_id, user.username, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND post.post_date > (UNIX_TIMESTAMP() - (7 * 86400))
    AND (user.is_moderator = 1 OR user.is_admin = 1)
ORDER BY count DESC

Something like that, off the top of my head. That looks for posts created in the last 7 days (7 * 86400).

 

[Brent W]

SELECT user.user_id, user.username, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND post.post_date > (UNIX_TIMESTAMP() - (7 * 86400))
    AND (user.is_moderator = 1 OR user.is_admin = 1)
ORDER BY count DESC

Something like that, off the top of my head. That looks for posts created in the last 7 days (7 * 86400).

For some reason that is only returning one user. I've tried it on multiple sites where there are clearly many moderators or admins with posts.

 

[Chris D]

SELECT user.user_id, user.username, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND post.post_date > (UNIX_TIMESTAMP() - (7 * 86400))
    AND (user.is_moderator = 1 OR user.is_admin = 1)
GROUP BY user.user_id
ORDER BY count DESC

You're not wrong. Try the above (added a GROUP BY user.user_id).

 

[Brent W]

Perfect. Thanks!

 

[AndyB]

@BamaStangGuy

You might like this add-on:

https://xenforo.com/community/resources/staff-activity.5194/