Rebuild User Post Counts (query)

[MySiteGuy]

MySiteGuy submitted a new resource:

Rebuild User Post Counts (query) - Quick and simple way update post counts for users.

This is a quick query which updates user post counts. It takes into consideration moderated and deleted status of threads and posts, as well as each forum's "Count messages posted in this forum toward user total" setting.

UPDATE xf_user AS user SET message_count = 
COALESCE(
 ( 
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER JOIN xf_forum AS forum ON forum.node_id = thread.node_id...

Read more about this resource...

 

[Mr Lucky]

Do I need to edit this at all? I am getting an error in phpMyAdmin

Error
SQL query: Documentation


UPDATE xf_user AS user SET message_count =
COALESCE(
 (
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER 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, 0
), 0 )
MySQL said: Documentation

#1054 - Unknown column '0' in 'group statement'

 

[MySiteGuy]

Sorry about that, I had a typo in the first query. It should be:

UPDATE xf_user AS user SET message_count =
COALESCE(
 (
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER 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
), 0 )

 

[wp_arun]

My site guy, you the man, this worked like a charm when I ran into user post count total needs to be updated.

 

[MySiteGuy]

I'm glad it was helpful. I have a few other queries like this that I need to add as Resources, when things slow down enough to have the time!

 

[wp_arun]

Back in the vB days, lynee was the one who used to play with SQL queries now we have you on xenforo All good, when you have time please share your resources.