Is there a way to rebuild post/profile-post like caches?
- Thread starter Jaxel
- Start date
Jake Bunce
Well-known 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.
I tested it myself, but of course you should backup first.
Jake Bunce
Well-known 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.
Jake Bunce
Well-known 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.
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.
Jake Bunce
Well-known member
Then you can make a small modification to this script for your purposes. Add the red code:
Rich (BB 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.
Gene
Member
That seems to have worked, but now when I run
It gives me numbers that are quite a bit lower than what XF is reporting. I just don't get it.
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 descJake Bunce
Well-known 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.
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
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.
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 39Similar threads
- Solved
- Question
- Question
