• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Rebuild user post counts (query)

Jake Bunce

XenForo moderator
Staff member
#1
Jake Bunce submitted a new resource:

Rebuild user post counts (query) (version 1.x) - A database query to rebuild all user post counts.

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...
Read more about this resource...
 

mistypants

Well-known member
#7
Quick question: I have several forums set to not add to the user's post count. Do I need to list them in the exclude forums bit, or will it do so automatically?

Thanks!
 

Mouth

Well-known member
#13
Perfect. Thanks!

Code:
mysql> 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 (78,80,112)
    -> AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
    -> GROUP BY post.user_id
    -> );
Query OK, 3473 rows affected, 9033 warnings (7.23 sec)
Rows matched: 23250  Changed: 3473  Warnings: 9033
 

AndreaMarucci

Well-known member
#14
Jake Bunce submitted a new resource:
Jake what should be the query if I want to recount the post for all forums but for a single user? I've seen that his one
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)
    LEFT JOIN xf_forum AS forum ON (forum.node_id = thread.node_id)
    WHERE post.user_id = user.user_id
    AND forum.node_id IS NOT NULL
    AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
    GROUP BY post.user_id
) WHERE user.user_id BETWEEN 1 AND 100;
is used if I want to recount for user from 1 to 100 but I'd need the exact syntax for a single user.

Thanks!
 

Brogan

XenForo moderator
Staff member
#15
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)
    LEFT JOIN xf_forum AS forum ON (forum.node_id = thread.node_id)
    WHERE post.user_id = user.user_id
    AND forum.node_id IS NOT NULL
    AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
    GROUP BY post.user_id
) WHERE user.user_id = 1;
 

AndreaMarucci

Well-known member
#18
Mmhh I've tried. The query was very fast but nothing is modified and I've tried on a user where I've personally deleted some messages. I've checked and the msg count is the same as before the query. is that correct?