Recalculate and update all user post counts (query)

[Jake Bunce]

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:

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:

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!

 

[Saeed]

Why thank you SOOO very much, Jake. You really know how to work wonders!

 

[Lu Kas]

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]

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:

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.

 

[Lu Kas]

I tried it, but my mysql returns "#1205 - Lock wait timeout exceeded; try restarting transaction"

 

[Jake Bunce]

I tried it, but my mysql returns "#1205 - Lock wait timeout exceeded; try restarting transaction"

Yeah. Big query is big.

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:

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]

Yeah. Big query is big.

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:

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]

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]

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!

 

[Lone Wolf]

Thanks Jake, this looks great.

This may seem stupid but how do I run the query?

 

[Paul B]

Via phpMyAdmin.

Click on the database on the left and then the SQL tab.

 

[steven s]

Just searched for the exact thing!

 

[grizone]

Jake Bunce, How to recalculate the number of user messages taking into account the settings of post counting in forum sections?

 

[Jake Bunce]

Jake Bunce, How to recalculate the number of user messages taking into account the settings of post counting in forum sections?

The second query in the first post lets you manually exclude certain forums from the count.

 

[grizone]

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]

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.