1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. This forum has been archived. New threads and replies may not be made. All add-ons/resources that are active should be migrated to the Resource Manager. See this thread for more information.

Recalculate and update all user post counts (query)

Discussion in 'Tips and Guides [Archive]' started by Jake Bunce, Sep 11, 2011.

  1. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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:

    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)
    	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:

    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)
    	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!
     
  2. Saeed

    Saeed Well-Known Member

    Why thank you SOOO very much, Jake. You really know how to work wonders! (y)
     
  3. Lu Kas

    Lu Kas Active Member

    does this solve the problem with double imports? i imported twice, deleted the old forums and so now my post count isn't correct.
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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:

    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
    );
    
    That should fix your problem with post counts following a double import.
     
    Vitor Dos Reis and Ronin Storm like this.
  5. Lu Kas

    Lu Kas Active Member

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

    Jake Bunce XenForo Moderator Staff Member

    Yeah. Big query is big. o_O

    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:

    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;
    
     
  7. Lu Kas

    Lu Kas Active Member

    thank you for your help, but i even geht the timeout when doing this between 1 and 3
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
    DarkSign likes this.
  9. lasertits

    lasertits Active Member

    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!
     
  10. Lone Wolf

    Lone Wolf Well-Known Member

    Thanks Jake, this looks great.

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

    Brogan XenForo Moderator Staff Member

    Via phpMyAdmin.

    Click on the database on the left and then the SQL tab.
     
  12. steven s

    steven s Well-Known Member

    Just searched for the exact thing!
     
  13. grizone

    grizone Member

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

    Jake Bunce XenForo Moderator Staff Member

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

    grizone Member

    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.
     
  16. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    I don't understand. The first query counts all posts in all forums. The second query lets you exclude certain forums from the count.
     

Share This Page