XF 1.5 Query For Admins & Moderators Stats

Brent W

Well-known member
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

XenForo developer
Staff member
Code:
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

Well-known member
Code:
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

XenForo developer
Staff member
Code:
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).
 
Top