• 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?

Jaxel

Well-known member
#1
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?
 

Jaxel

Well-known member
#3
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:
Tony T, Inui, (deleted user) and 1 other person like this.
 
#9
...and it added all the likes applied to private convo messages to total like counts. Good thing I backed up!
 

Jake Bunce

XenForo moderator
Staff member
#10
It gave me a
504 Gateway Time-out
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).

...and it added all the likes applied to private convo messages to total like counts. Good thing I backed up!
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
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.
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.
 

Jake Bunce

XenForo moderator
Staff member
#12
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.
The script would need to specify post and profile_post to work for my site.
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.
 
#13
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.
 

Jake Bunce

XenForo moderator
Staff member
#14
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.
 
#15
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.
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.
 
#20
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