Jake Bunce
Well-known member
This will recalculate and update the post counts for all users at once. This query can be slow on large forums with lots of posts:
If you have forums you wish to exclude from the count then use this query, where 1,2,3 is a comma-list of node_ids that are excluded from the count:
Backup first!
Rich (BB code):
UPDATE xf_user AS user
SET message_count = (
SELECT COUNT(*)
FROM xf_post AS post
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE post.user_id = user.user_id
AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
GROUP BY post.user_id
);
If you have forums you wish to exclude from the count then use this query, where 1,2,3 is a comma-list of node_ids that are excluded from the count:
Rich (BB code):
UPDATE xf_user AS user
SET message_count = (
SELECT COUNT(*)
FROM xf_post AS post
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE post.user_id = user.user_id
AND thread.node_id NOT IN (1,2,3)
AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
GROUP BY post.user_id
);
Backup first!