Remove Specific "Store View" settings for products

While setting up a CSV to import products through Magmi, instead of putting “admin” in the “store” column, I put the name of a “store view.”

This in turn has now set unchecked the “use default” and put duplicate entries in the store view for those product. This is becoming an issue when disabling products through import, updating prices, etc..

Is there any way through PHP/SQL to programmatically remove these custom settings for a store view, while limiting the products it is affecting? I have the same prefix on all SKUs affected, and some have already been “cured.”

EDIT: THIS ANSWER WORKED FOR ME

<?php

include 'app/Mage.php'; Mage::app();

/** store id for which you would like to KEEP values */
$defaultStoreId = Mage_Catalog_Model_Product::DEFAULT_STORE_ID;

/** @var Mage_Catalog_Model_Product $model */
$model        = Mage::getModel('catalog/product');
$resource     = $model->getResource();
$entityType   = $resource->getEntityType();
$entityTypeId = $entityType->getEntityTypeId();
$adapter      = $resource->getWriteConnection();

/** gather the names of all tables that contain the attribute values to be removed */
$tables = array();

/** get all attribute value tables, making sure to leave out catalog_product_entity */
$attributes = $entityType->getAttributeCollection();
$attributes->addFieldToFilter('backend_type', array('neq' => 'static'));
foreach($attributes as $attribute) {
    /** @var Mage_Catalog_Model_Resource_Eav_Attribute $attribute */
    $tables[] = $attribute->getBackendTable();
}
$tables = array_unique($tables);

/** get product ids of all products whose SKU begins with 16- */
$collection = $model->getCollection();
$collection->addFieldToFilter('sku', array('like' => '16-%'));
$productIds = $collection->getAllIds();

$valuesDeletedCount = 0;
foreach($tables as $table) {
    $condition = array(
        'entity_type_id = ?' => $entityTypeId, // here we ensure that we are only dealing with values belonging to PRODUCTS
        'entity_id IN(?)'    => $productIds, // here we make sure that we are only dealing with SKUs beginning with 16-
        'store_id != ?'      => $defaultStoreId, // here we make sure we are deleting from ALL stores except for DEFAULT
    );
    $valuesDeletedCount += $adapter->delete($table, $condition);
}
echo $valuesDeletedCount . ' non-default attribute values deleted.';

?>

saved as “reset_defaults.php” in my “/magento” directory

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

Here is a more programattic method. It still skirts Magento’s save() methods and the beforeSave/afterSave events but this works great and I use it sometimes in development to reset product data.

<?php 

    /** store id from which you would like to remove values */
    $storeId = 1;

    /** example attribute codes that you would to remove values for */
    $attributeCodesToReset = array('name', 'description');

    $model        = Mage::getModel('catalog/product');
    $resource     = $model->getResource();
    $entityTypeId = $resource->getEntityType()->getEntityTypeId();
    $adapter      = $resource->getWriteConnection();

    /** use collection(filter it appropriately) to grab product ids */
    $collection = $model->getCollection();
    $collection->addFieldToFilter('entity_id', 886);
    $collection->setStoreId($storeId);
    $productIds = $collection->getAllIds();

    foreach($attributeCodesToReset as $attributeCode) {
        $attribute = $resource->getAttribute($attributeCode);
        $condition = array(
            'entity_id IN(?)'    => $productIds,
            'attribute_id = ?'   => $attribute->getId(),
            'store_id = ?'       => $storeId,
            'entity_type_id = ?' => $entityTypeId
        );
        $adapter->delete($attribute->getBackendTable(), $condition);
    }

Update

This is how you would delete all attribute values from ALL NON-DEFAULT stores for products whose SKU begins with ’16-‘

/** store id for which you would like to KEEP values */
$defaultStoreId = Mage_Catalog_Model_Product::DEFAULT_STORE_ID;

/** @var Mage_Catalog_Model_Product $model */
$model        = Mage::getModel('catalog/product');
$resource     = $model->getResource();
$entityType   = $resource->getEntityType();
$entityTypeId = $entityType->getEntityTypeId();
$adapter      = $resource->getWriteConnection();

/** gather the names of all tables that contain the attribute values to be removed */
$tables = array();

/** get all attribute value tables, making sure to leave out catalog_product_entity */
$attributes = $entityType->getAttributeCollection();
$attributes->addFieldToFilter('backend_type', array('neq' => 'static'));
foreach($attributes as $attribute) {
    /** @var Mage_Catalog_Model_Resource_Eav_Attribute $attribute */
    $tables[] = $attribute->getBackendTable();
}
$tables = array_unique($tables);

/** get product ids of all products whose SKU begins with 16- */
$collection = $model->getCollection();
$collection->addFieldToFilter('sku', array('like' => '16-%'));
$productIds = $collection->getAllIds();

$valuesDeletedCount = 0;
foreach($tables as $table) {
    $condition = array(
        'entity_type_id = ?' => $entityTypeId, // here we ensure that we are only dealing with values belonging to PRODUCTS
        'entity_id IN(?)'    => $productIds, // here we make sure that we are only dealing with SKUs beginning with 16-
        'store_id != ?'      => $defaultStoreId, // here we make sure we are deleting from ALL stores except for DEFAULT
    );
    $valuesDeletedCount += $adapter->delete($table, $condition);
}
echo $valuesDeletedCount . ' non-default attribute values deleted.';

Solution 2

This is best done with a few SQL queries (don’t forget to make a backup before!)

Let’s say your store view has the ID 1 (if you don’t know the ID, you can look it up in the core_store table) and the SKU prefix is p_

Then you have to remove all rows from the attribute tables with store_id=1 and entity_id IN ( [product ids where sku starts with "p_" ]).

For varchar attributes, the query is

DELETE FROM catalog_product_entity_varchar 
    WHERE store_id=1
    AND entity_id IN (
        SELECT * from catalog_product_entity WHERE sku LIKE "p\_%"
    );

Do the same for the other catalog_product_entity_* attribute tables.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply