• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Rebuild user post counts (query)

AndreaMarucci

Well-known member
#21
Suppose a user has 1000 messages but 300 of them were deleted for one reason or another. I'd like that in the postbit of the user there are 700 messages and not 1000 so I'm trying to make XF recount the users's messages not including the deleted ones...
 

CFodder

Well-known member
#24
Worked very nicely on 1.2.3 thanks ... our forum didn't count posts for 5 years, amazing how much everyone's counts jumped up lol :).

Thanks for the sql query Jake.
 

Mouth

Well-known member
#27
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
Is it possible to incorporate into this, or have another SQL query, that will identify the nodes that are not set to be included into post counts?
 

Jake Bunce

XenForo moderator
Staff member
#28
Is it possible to incorporate into this, or have another SQL query, that will identify the nodes that are not set to be included into post counts?
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 post.message_state = 'visible' AND thread.discussion_state = 'visible'
    AND forum.count_messages = 1
	GROUP BY post.user_id
);
 
#29
That already happens automatically when a post is deleted.
The count is reduced.
If I understand correct, user have 10 messages, when I delete one message, it automatically show 9 messages?

Then I don't understand, why if I deleted messages from one user, then it still show 204 messages moc.jpg , but if I try find all his messages, it show only 81 malo.jpg ?
 

Jake Bunce

XenForo moderator
Staff member
#30
If I understand correct, user have 10 messages, when I delete one message, it automatically show 9 messages?

Then I don't understand, why if I deleted messages from one user, then it still show 204 messages View attachment 92624 , but if I try find all his messages, it show only 81 View attachment 92623 ?
The search features use the search index which might need rebuilding:

Admin CP -> Tools -> Rebuild Caches -> Rebuild Search Index

Or maybe you don't have permission to view some of his posts.

Or maybe his post count is wrong. Run this query in this guide to recalculate it.
 
#31
I have permissions, I am super admin. ;-) And 81 messages is correct number, 204 is wrong but still showing. Rebuild Search Index was first thing what i did.
 
#34
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 post.message_state = 'visible' AND thread.discussion_state = 'visible'
    AND forum.count_messages = 1
    GROUP BY post.user_id
);
Hi Jake, I ran that query and got the following output:

Code:
Query OK, 12 rows affected, 22294 warnings (3 min 38.10 sec)
Rows matched: 54511  Changed: 12  Warnings: 22294
Is it normal to have 22294 warnings? When I run "SHOW WARNINGS\G" I get a lot of these:

Code:
*************************** 1. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'message_count' cannot be null
*************************** 2. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'message_count' cannot be null
*************************** 3. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'message_count' cannot be null
Are these safe to ignore?

Oh and btw, the query said that there were 54511 rows matched, however according to XF statistics I only have 44,104 users. Is the mismatch normal?