Resource icon

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.

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...
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?
 
Top