Rebuild Likes (Changed username or deleted user)

[Fatih]

Hi there,

If you change a username, his/her username don't updated in a Showcase Item, Review, Comment or Comment Reply Likes.

If you delete a user, his/her username don't deleted from Showcase Item, Review, Comment or Comment Reply Likes.

Jake Bunce shared a Query for post and profile posts at XenForo. Well, works pretty but I changed it with these codes...

<?php

$startTime = microtime(true);
$fileDir = dirname(__FILE__);

require($fileDir . '/library/XenForo/Autoloader.php');
XenForo_Autoloader::getInstance()->setupAutoloader($fileDir . '/library');

XenForo_Application::initialize($fileDir . '/library', $fileDir);
XenForo_Application::set('page_start_time', $startTime);

####################

ini_set('max_execution_time', 5000);
ini_set('memory_limit', -1);
ini_set('display_errors', true);
ini_set('display_startup_errors', true);

$db = XenForo_Application::get('db');

$batchSize = 1000;

$contentTypes = array(
    'showcase_review',
    'showcase_item',
    'showcase_comment',
    'showcase_comment_reply'
);

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
    )
");

echo "done!<br /><br />";

foreach ($contentTypes AS $contentType)
{
    echo "Rebuilding like totals for {$contentType}s . . . ";

    // REBUILD LIKE TOTALS FOR CONTENT
    $db->query("
        UPDATE xf_{$contentType} AS content
        SET content.likes = (
            SELECT COUNT(*)
            FROM xf_liked_content AS lc
            INNER JOIN xf_user AS u ON (u.user_id = lc.like_user_id)
            WHERE lc.content_type = ?
            AND lc.content_id = content.{$contentType}_id
        )
    ", $contentType);

    echo "done!<br />";

    $totalIds = $db->fetchOne("
        SELECT COUNT(*)
        FROM xf_{$contentType}
        WHERE likes > 0
    ");

    echo "Rebuilding serial cache for {$contentType}s . . . ";

    // REBUILD SERIAL CACHE OF RECENT LIKERS

    // FOR ZERO LIKES
    $db->query("
        UPDATE xf_{$contentType}
        SET like_users = 'a:0:{}'
        WHERE likes = 0
    ");

    // FOR NONZERO LIKES
    for ($offset = 0; $offset < $totalIds; $offset += $batchSize)
    {
        $ids = $db->fetchCol("
            SELECT {$contentType}_id
            FROM xf_{$contentType}
            WHERE likes > 0
            ORDER BY {$contentType}_id
            ASC
            LIMIT {$offset}, {$batchSize}
        ");

        foreach ($ids AS $id)
        {
            $likes = $db->fetchAll("
                SELECT lc.like_user_id, u.username
                FROM xf_liked_content AS lc
                INNER JOIN xf_user AS u ON (u.user_id = lc.like_user_id)
                WHERE lc.content_type = ?
                AND lc.content_id = ?
                ORDER BY lc.like_date
                DESC
                LIMIT 0, 5
            ", array($contentType, $id));

            $writeVal = array();
            foreach ($likes AS $like)
            {
                $writeVal[] = array(
                    'user_id' => $like['like_user_id'],
                    'username' => $like['username']
                );
            }

            $writeVal = serialize($writeVal);

            $db->query("
                UPDATE xf_{$contentType}
                SET like_users = ?
                WHERE {$contentType}_id = ?
            ", array($writeVal, $id));

            unset($id, $likes, $like, $writeVal);
        }

        unset($ids);
    }

    echo "done!<br /><br />";
}

echo "All done!";

Now run on browser site.com/rebuildlikes.php and getting the error...

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Table 'dbexmp.xf_showcase_review' doesn't exist - library/Zend/Db/Statement/Mysqli.php:77

I'm not sure it's a bug or as design but this should be fix or @Bob if you edit this php file, I'd use it.

Regards,

 

[Bob]

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Table 'dbexmp.xf_showcase_review' doesn't exist - library/Zend/Db/Statement/Mysqli.php:77

xf_showcase_review <--- that is NOT the correct table name, its xf_nflj_showcase_review

you need to replace all query instances of xf_{$contentType} with xf_nflj_{$contentType}

If you change a username, his/her username don't updated in a Showcase Item, Review, Comment or Comment Reply Likes.

it SHOULD be, if its not, then there is a new bug. Which VERSION of showcase are you running?

 

[Fatih]

Bob,

2.5.x or newer version there is no difference so you can try it with a username but I will try it (xf_nflj_{$contentType}) thank you

 

[Fatih]

I tried it now but problem still persists...

 

[Bob]

I tried it now but problem still persists...

Its probably because you are trying to use something JAKE wrote to be generic and it expects the TABLE NAMES to be named EXACTLY like the CONTENT TYPE (which does not work with addons that use prefixes as recommended by MIKE and KIER as well as when table names do not exact match the content type name).

You will need to send JAKE an inbox giving him the below information so that he can re write it for you.

Content Type: showcase_comment
Table Name: xf_nflj_showcase_comment

Content Type: showcase_comment_reply
Table Name: xf_nflj_showcase_comment_reply

Content Type: showcase_item
Table Name: xf_nflj_showcase_item

Content Type: showcase_review
Table Name: xf_nflj_showcase_rate_review