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?
Tony T, Inui, (deleted user) and 1 other person like this.
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).It gave me a
504 Gateway Time-out
I don't believe XF allows liking of PMs....and it added all the likes applied to private convo messages to total like counts. Good thing I backed up!
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.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.
Then you can make a small modification to this script for your purposes. Add the red code:The script would need to specify post and profile_post to work for my site.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.
$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) . ") ) ");
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
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.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.
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