1. REGISTRATION REQUIREMENTS:

    Register with your xenforo.com Username then start a conversation at xenforo.com w/Bob and provide the following:
    1. Your XenForo License Validation Token
    2. The Domain Name associated with the License
    NOTE: Your account will be validated once ALL requirements are verified/met. Thank you for your patience.

Rebuild Likes (Changed username or deleted user)

Discussion in 'Showcase Support' started by fatih, May 20, 2016.

  1. fatih

    fatih Member Showcase Premium

    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...

    Code:
    <?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...

    Code:
    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,
     
  2. Bob

    Bob Developer Staff Member

    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}

    it SHOULD be, if its not, then there is a new bug. Which VERSION of showcase are you running?
     
    Last edited: May 20, 2016
    fatih likes this.
  3. fatih

    fatih Member Showcase Premium

    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 :)
     
  4. fatih

    fatih Member Showcase Premium

    I tried it now but problem still persists...
     
  5. Bob

    Bob Developer Staff Member

    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
     
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.