ALERT!
Click here to register with a few steps and explore all our cool stuff we have to offer!
Home
Upgrade
Credits
Help
Search
Awards
Achievements
 8391

Rebuild user post counts (query)

by Bi0S - 05-04-2017 - 03:02 AM
#1
This will recalculate and update the post counts for all users at once. This is a large query that may take a while to finish.
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:
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.

Because this query is so large you may encounter errors relating to these MySQL settings:

wait_timeout
innodb_lock_wait_timeout


You need to ask your host or server person to increase these settings if you encounter such errors.
Reply
#2
This just repairs it?
Reply
#3
Thanks for tips, it save me :)
Reply

Users browsing: 1 Guest(s)