SQL? to make product reviews available in other store

Question

we just opened a new shop - and have some long time products in here with many reviews.

I just now realized that reviews are a member of a store. SO in the new store they do not shop up.

I had a look at the table review_store and see the relation here.

What I probably need to do is query: select every review, where store review is in [0,1,2,4,7,8] and insert into table the review relation again for all stores except the one that we are selecting from (so for say store 0, we insert all stores except 0)

Sounds like a pretty difficult query. Anyone done this before?

There is no select-all, update all for reviews (other then status)

Help appreciated

Answer 1

Create following shell script in the Magento shell folder, name it as review.php:

<?php
require_once 'abstract.php';
class Mage_Shell_Review extends Mage_Shell_Abstract
{

    public function run()
    {
        $ids    = Mage::getModel('review/review')->getCollection()->getAllIds();
        $stores = Mage::getModel('core/store')
            ->getCollection()
            ->getAllIds();

        $adapter = Mage::getSingleton('core/resource')->getConnection('core_write');

        $table = 'review_store';
        try {
            $adapter->beginTransaction();
            $adapter->truncateTable($table);
            $values=array();
            foreach ($ids as $id) {
                foreach ($stores as $storeId) {
                    $values[]="($id,$storeId)";
                }
            }
            $values=implode(",\n",$values);
            $query="INSERT INTO $table (`review_id`,`store_id`) VALUES $values;";
            $adapter->query($query);
            $adapter->commit();
            Mage::app()->getCacheInstance()->flush();
            Mage::app()->cleanCache();
            echo "OK\n";
        } catch (Exception $e) {
            $adapter->rollBack();
            print_r($e->getTrace());
        }
    }
}

$shell = new Mage_Shell_Review();
$shell->run();

And run the script php -f shell/review.php

Answer 2

We ended up using this code. Place in shell or other protected subdirectory from root and run from web. Remember to remove when you are done.

• does not truncate table
• looks for existing reviews and copies them on a per store basis
• carefully preventing duplicate creation
• option to output the SQL code for manual execution

!!! PLEASE BACKUP YOUR REVIEW TABLE BEFORE EXECUTING

• when you are done, open up all reviews in Magento
• Filter on Approved
• Select all -the mass update Set Status => approved
• This will trigger aggregation and make them available in counts too

Good luck!

<?php

error_reporting(~0); ini_set('display_errors', 1);
// adjust for where your file is, ours is in protected subdirectory of /
define('MAGENTO_ROOT', dirname(dirname(__FILE__)));
$mageFilename = MAGENTO_ROOT . '/app/Mage.php';
require_once $mageFilename;
umask(0);
Mage::app();

$resource = Mage::getSingleton('core/resource');
$adapter = $resource->getConnection('core_write');
$prefix = Mage::getConfig()->getTablePrefix();
$tableName = $prefix.'review_store';

$values = array();

$query = 'SELECT * FROM ' . $tableName;
$ids = $adapter->fetchAll($query);

// copy_reviews_tostore(idlist,valuelist,from_storeid_here,to_storeid_array_here)
copy_reviews_tostore($ids,$values,1,array(8));
copy_reviews_tostore($ids,$values,7,array(2,8));
copy_reviews_tostore($ids,$values,2,array(8));

try {
        foreach ($values as $value) { $result .= "(".$value['review_id'].",".$value['store_id'].")".",\n"; }
        $result=substr($result, 0, -2);
        $query="INSERT INTO $tableName (`review_id`,`store_id`) VALUES $result;";
        // if you want to copy-paste manual to PhpMyadmin
        echo($query);
        // if you want to execute directly
        //$adapter->beginTransaction();
        //$adapter->query($query);
        //$adapter->commit();
        echo "OK\n";
    } catch (Exception $e) {
        $adapter->rollBack();
        print_r($e->getTrace());
    }

function copy_reviews_tostore ($ids, &$values, $from_store, $to_stores) {

    foreach ($ids as $id) {
        $review_id = $id['review_id'];
        $store_id = $id['store_id'];
        if($store_id != $from_store) { continue; }
        foreach ($to_stores as $store) {
            // do not write to same store
            if($store_id == $store) { continue; }
            // check for already existing review/store value in DB
            if (check_review_exists($ids, $store, $review_id)) { continue; }
            // check for already existing review/store in write buffer (from previous cycle)
            $skip = false;
            foreach ($values as $value) {
                if (empty($values) || ($id['review_id'] == $value['review_id'] && $store == $value['store_id'])) 
                    { $skip = true; }
            }
            if (!$skip) { $values[]= array("review_id" => $review_id,"store_id" => $store); }
        }
    }
}

function check_review_exists ($ids, $find_store_id, $find_review_id) {
    $ret = false;
    foreach ($ids as $id) {
        $review_id = $id['review_id'];
        $store_id = $id['store_id'];                    
        if ($review_id == $find_review_id && $store_id == $find_store_id) { $ret = true; }
    }
return $ret;
}