1. 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)

A database query to rebuild all user post counts.

  1. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Jake Bunce submitted a new resource:

    Rebuild user post counts (query) (version 1.x) - A database query to rebuild all user post counts.

    Read more about this resource...
     
    oloman likes this.
  2. DBA

    DBA Well-Known Member

    Every time I run this query it drastically increases the users post count.
     
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  4. X_Plaza

    X_Plaza Active Member

    Using that won't update latest post ?
     
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    It only updates the post counts of your users.
     
  6. DRE

    DRE Well-Known Member

    101 rows affected. ( Query took 0.3494 sec )


    ^^^Doesn't seem that large of a query at all.
     
  7. mistypants

    mistypants Well-Known Member

    Quick question: I have several forums set to not add to the user's post count. Do I need to list them in the exclude forums bit, or will it do so automatically?

    Thanks!
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    List them.
     
    mistypants likes this.
  9. Mouth

    Mouth Well-Known Member

    The query won't run for me ... gives "Column 'message_count' cannot be null"
     
  10. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Where / how are you running this query? It looks like the connection is using strict mode.
     
  11. Mouth

    Mouth Well-Known Member

    Using mysql binary from command line ... same result when I use phpmyadmin too
     
  12. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  13. Mouth

    Mouth Well-Known Member

    Perfect. Thanks!

    Code:
    mysql> 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 (78,80,112)
        -> AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
        -> GROUP BY post.user_id
        -> );
    Query OK, 3473 rows affected, 9033 warnings (7.23 sec)
    Rows matched: 23250  Changed: 3473  Warnings: 9033
     
  14. AndreaMarucci

    AndreaMarucci Well-Known Member

    Jake what should be the query if I want to recount the post for all forums but for a single user? I've seen that his one
    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;
    is used if I want to recount for user from 1 to 100 but I'd need the exact syntax for a single user.

    Thanks!
     
  15. Brogan

    Brogan XenForo Moderator Staff 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 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 = 1;
     
    kontrabass and Jake Bunce like this.
  16. AndreaMarucci

    AndreaMarucci Well-Known Member

  17. Brogan

    Brogan XenForo Moderator Staff Member

    Be aware that query doesn't take into account forums which don't count posts.
     
  18. AndreaMarucci

    AndreaMarucci Well-Known Member

    Mmhh I've tried. The query was very fast but nothing is modified and I've tried on a user where I've personally deleted some messages. I've checked and the msg count is the same as before the query. is that correct?
     
  19. AndreaMarucci

    AndreaMarucci Well-Known Member

    I count messages in all the forums
     
  20. Brogan

    Brogan XenForo Moderator Staff Member

    This query won't restore deleted posts nor include them in the count.

    What are you trying to do?
     

Share This Page