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

Is there a way to rebuild post/profile-post like caches?

Discussion in 'XenForo Questions and Support' started by Jaxel, Dec 21, 2012.

  1. Jaxel

    Jaxel Well-Known Member

    I just deleted an alt user who went on a liking rampage... I don't want his username, which was offensive, appearing under every post. Is there a way to rebuild the like caches?
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    You might have to remove the likes from the database.

    DELETE FROM xf_liked_content WHERE user_id = X
     
  3. Jaxel

    Jaxel Well-Known Member

    I did that... doesn't remove the person's username from the like_cache, since likes are cached in a blob on xf_post and xf_profile_post.

    In addition, now when someone likes one of these posts, it says:
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    This is an infrequent but persistent problem for many people, so I just created a script for it. This will rebuild the total likes and the serial blob of user info. Upload this to your forum root and run it from your browser.

    I tested it myself, but of course you should backup first.
     

    Attached Files:

    pt.develop, howarde, Helladen and 9 others like this.
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Minor fix. I added inner joins so that nonexistent users are excluded. XenForo doesn't delete the like records when you delete a user, so I have now accounted for that.
     

    Attached Files:

    BamaStangGuy, sheel, Peace and 25 others like this.
  6. Jaxel

    Jaxel Well-Known Member

    Worked perfectly! Thank you Jake.
     
    caoanh204 and Jake Bunce like this.
  7. caoanh204

    caoanh204 Active Member

  8. Gene

    Gene Member

    It gave me a
    504 Gateway Time-out
     
  9. Gene

    Gene Member

    ...and it added all the likes applied to private convo messages to total like counts. Good thing I backed up!
     
  10. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    It can take a while to complete if your forum is more than small or mid-sized. Obviously it's taking longer to complete than your server will allow. Increase the timeout on the server (contact your host or server person).

    I don't believe XF allows liking of PMs.

    This script includes all content types when rebuilding the like counts in user records. So if you have addons installed that allow liking of more content types then those will be included in a user's total likes. It then specifically rebuilds counts and serial blobs for posts and profile posts.
     
  11. Gene

    Gene Member

    Yeah, I have conversation essentials installed. The script would need to specify post and profile_post to work for my site. Ugh. Now we're down because my VPS is being difficult. What a nightmare.
     
  12. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Then you can make a small modification to this script for your purposes. Add the red code:

    Code:
    $contentTypes = array(
    	'profile_post',
    	'post'
    );
    
    echo "Rebuilding like totals for users . . . ";
    
    // REBUILD LIKE TOTALS FOR USERS
    $db->query("
    	UPDATE xf_user AS u
    	SET u.like_count = (
    		SELECT COUNT(*)
    		FROM xf_liked_content AS lc
    		INNER JOIN xf_user_authenticate AS ua ON (ua.user_id = lc.like_user_id)
    		WHERE lc.content_user_id = u.user_id
    		AND lc.content_type IN (" . $db->quote($contentTypes) . ")
    	)
    ");
    
    That will make it so the user counts only include likes for posts and profile posts.
     
    Sasa and Gene like this.
  13. Gene

    Gene Member

    That seems to have worked, but now when I run
    Code:
    select u.username, count(like_id) as like_count
    from xf_liked_content l
    inner join xf_user u on (u.user_id = l.content_user_id)
    inner join xf_post p on (p.post_id = l.content_id)
    where (content_type = "post" OR content_type = "profile_post")
    group by content_user_id
    order by like_count desc
    It gives me numbers that are quite a bit lower than what XF is reporting. I just don't get it.
     
  14. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    You would need to do separate queries for post likes and profile post likes if you want to inner join the content record. Right now you are inner joining xf_post for both posts and profile posts. Don't mix the content types.

    Or you can remove the inner join on xf_post. If you don't join the content record then you can aggregate the count for both types. The only reason to inner join would be to ensure that physically deleted posts and profile posts are not counted.
     
    Gene likes this.
  15. Gene

    Gene Member

    My bad - that was an artifact from an earlier version of the query where I was using something from xf_post. And sure enough, by your advice, removing that inner join got the numbers where they needed to be! Thanks Jake, you rock.
     
  16. Valter

    Valter Active Member

    Can this be optimized to avoid time-out errors? Process X results at a time? Thanks.
     
    Sasa likes this.
  17. Sasa

    Sasa Active Member

    @Jake, this rebuild likes for all users?
     
  18. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Yes.
     
    Sasa likes this.
  19. Sasa

    Sasa Active Member

    Thank you. :)
     
  20. eagle eyes

    eagle eyes Active Member

    Getting this.

    Code:
    An exception occurred: Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction in /home/xxx/public_html/library/Zend/Db/Statement/Mysqli.php on line 214
    
    Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
    Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
    Zend_Db_Adapter_Abstract->query() in /home/xxx/public_html/rebuildlikes.php at line 39
     

Share This Page