Resource icon

Rebuild user post counts (query)

Jake Bunce

Well-known member
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...
 
101 rows affected. ( Query took 0.3494 sec )


^^^Doesn't seem that large of a query at all.
 
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!
 
Something to try:
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
 
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!
 
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;
 
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?
 
This query won't restore deleted posts nor include them in the count.

What are you trying to do?
 
Top Bottom