Resource icon

Rebuild user post counts (query)

AndreaMarucci

Well-known member
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...
 

Brogan

XenForo moderator
Staff member
That already happens automatically when a post is deleted.
The count is reduced.
 

CFodder

Well-known member
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
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

Well-known member
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
);
 

AscalonCZ

Active member
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 messagesmoc.jpg , but if I try find all his messages, it show only 81malo.jpg ?
 

Jake Bunce

Well-known member
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 messagesView attachment 92624 , but if I try find all his messages, it show only 81View 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.
 

AscalonCZ

Active member
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.
 

xf2work

New member
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?
 

Wildcat Media

Well-known member
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
);

This should work in XF 2, as I doubt table names have changed.

But do we even need to do this anymore, even in 1.5.24? Is rebuilding post counts now part of Rebuild User Caches?
 
Top