• This forum has been archived. New threads and replies may not be made. All add-ons/resources that are active should be migrated to the Resource Manager. See this thread for more information.

Recalculate and update all user post counts (query)

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:

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!
 
does this solve the problem with double imports? i imported twice, deleted the old forums and so now my post count isn't correct.
 
does this solve the problem with double imports? i imported twice, deleted the old forums and so now my post count isn't correct.

It probably won't fix that problem because of this:

http://xenforo.com/community/threads/future-fix-deleting-a-forum-doesnt-delete-its-threads.9743/

Here is a new query for you which excludes orphaned threads:

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)
	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
);

That should fix your problem with post counts following a double import.
 
I tried it, but my mysql returns "#1205 - Lock wait timeout exceeded; try restarting transaction"
 
I tried it, but my mysql returns "#1205 - Lock wait timeout exceeded; try restarting transaction"

Yeah. Big query is big. O_o

Try using this new query to break the job into batches. Specify a range of user_ids (the numbers in red). Keep using a smaller range until the query completes. Then run the same query over and over using higher ranges to cover the rest of the user_ids:

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)
	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;
 
Yeah. Big query is big. o_O

Try using this new query to break the job into batches. Specify a range of user_ids (the numbers in red). Keep using a smaller range until the query completes. Then run the same query over and over using higher ranges to cover the rest of the user_ids:

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)
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;

thank you for your help, but i even geht the timeout when doing this between 1 and 3
 
I tried it, but my mysql returns "#1205 - Lock wait timeout exceeded; try restarting transaction"

If you have root access to the server then you can edit the my.cnf file (usually at /etc/my.cnf) to increase the value of innodb_lock_wait_timeout. Increasing that limit should avoid this error.
 
This is exactly what I needed! Was doing a lot of testing before porting and knew I had to run some queries to fix this eventually. Thanks!
 
What should I do if I want to recalculate the message in those sections in which they had not taken into account? I can not exclude such sections.
 
What should I do if I want to recalculate the message in those sections in which they had not taken into account? I can not exclude such sections.

I don't understand. The first query counts all posts in all forums. The second query lets you exclude certain forums from the count.
 
Back
Top Bottom