• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
  • 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

XenForo moderator
Staff member
#1
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:

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!
 

Lu Kas

Active member
#3
does this solve the problem with double imports? i imported twice, deleted the old forums and so now my post count isn't correct.
 

Jake Bunce

XenForo moderator
Staff member
#4
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:

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.
 

Jake Bunce

XenForo moderator
Staff member
#6
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:

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;
 

Lu Kas

Active member
#7
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:

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
 

Jake Bunce

XenForo moderator
Staff member
#8
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.
 

lasertits

Active member
#9
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!
 
#15
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.
 

Jake Bunce

XenForo moderator
Staff member
#16
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.