Removing needless product attribute values

Recently, we have faced with an issue that looked like a default Magento behavior. It turns out that Magento keeps the product attribute value even if the attribute was removed from the current attribute set. This brief article describes the issue’s details and a shell script on how to remove the old unnecessary attribute values from the database.

First of all, let’s look at the example of the issue. We’ve added the attribute “manufacturer” to the “Shirts T” attribute set on our clean test Magento installation with the sample data. Then, we have set the “manufacturer” to the value “some manufacturer” for the product: “The Get Up Kids: Band Camp Pullover Hoodie”. That is a usual process on all Magento stores.

Some time later, we decided that we do not need the attribute “manufacturer” in the “Shirts T” attribute set and removed it from there. Then, we performed product attributes reindex and flushed the cache. Of course, we think that it is normal behavior if we do not see the “New Manufacturer” product attribute in our “The Get Up Kids: Band Camp Pullover Hoodie” product. And it is really true for the backend part and product details. However, note an important thing – the “manufacturer” attribute is used in the layered navigation for other attribute sets. So, we can find it if we look at the category with our “The Get Up Kids: Band Camp Pullover Hoodie” product if there is no other product with this attribute present:

2014-10-09_1749 2014-10-09_1750

We’ve limited our example only with two screenshots, but you can check this behaviour by yourself too and get the same result.

That is why, we recommend to use a shell script. It will clean up the product attribute values for the attributes that are not present in the current product attribute set and are user defined (we do not want to remove the system attributes). We would like to apologize for the direct MySQL query, but there is no need to use a model for that table. We just want to leave it all in one file instead of creating a new extension. Simply put the file named fix_attributes.php (or any other file’s name that you like) with the following code to your_magento_root/shell:

<?php

require_once 'abstract.php';

class Mage_Shell_Fix_Attributes extends Mage_Shell_Abstract
{
    const PAGE_SIZE = 100;

    /**
     * Parse string with id's and return array
     *
     * @param string $string
     * @return array
     */
    protected function _parseString($string)
    {
        $ids = array();
        if (!empty($string)) {
            $ids = explode(',', $string);
            $ids = array_map('trim', $ids);
        }
        return $ids;
    }
    /**
     * Run script
     *
     */
    public function run()
    {
        if ($this->getArg('products'))
        {
            //allowed attribute types
            $types = array('varchar', 'text', 'decimal', 'datetime', 'int');

            //attribute sets array
            $attributeSets = array();

            //user defined attribute ids
            $entityType = Mage::getModel('eav/entity_type')->loadByCode('catalog_product');

            //connection for raw queries
            $connection = Mage::getSingleton('core/resource')->getConnection('core_write');

            $attributeCollection = $entityType->getAttributeCollection()->addFilter('is_user_defined','1')->getItems();
            $attrIds = array();
            foreach($attributeCollection as $attribute) {
                $attrIds[] = $attribute->getId();
            }
            $userDefined = implode(',', $attrIds);

            //product collection
            $collection = Mage::getModel('catalog/product')->getCollection();
            $entityTable = $collection->getTable(Mage::getModel('eav/entity_type')->loadByCode('catalog_product')->getEntityTable());
            if($this->getArg('products') != 'all'){
                if($ids = $this->_parseString($this->getArg('products'))) {
                    $collection->addAttributeToFilter('entity_id', array('in' => $ids));
                }
            }
            $collection->setPageSize(self::PAGE_SIZE);

            $pages = $collection->getLastPageNumber();
            $currentPage = 1;

            //light product collection iterating
            while($currentPage <= $pages) {
                $collection->setCurPage($currentPage);
                $collection->load();

                foreach($collection->getItems() as $item) {
                    $product = Mage::getModel('catalog/product')->load($item->getId());

                    //updating attribute ids for current products attribute set if necessary
                    if(!isset($attributeSets[$product->getAttributeSetId()])) {
                        $attributes = Mage::getModel('catalog/product_attribute_api')->items($product->getAttributeSetId());
                        $attrIds = array();
                        foreach($attributes as $attribute) {
                            $attrIds[] = $attribute['attribute_id'];
                        }
                        $attributeSets[$product->getAttributeSetId()] = implode(',', $attrIds);
                    }

                    //deleting extra product attributes values for each backend type
                    foreach($types as $type) {
                        $sql = 'DELETE FROM `' . $entityTable . '_' . $type . '`
                                WHERE `entity_id` = ' . $product->getId() . '
                                    AND attribute_id NOT IN (' . $attributeSets[$product->getAttributeSetId()] . ')
                                    AND attribute_id IN (' . $userDefined . ')';
                        $connection->query($sql);
                    }
                }

                $currentPage++;
                $collection->clear();
            }

            echo 'Done!' . PHP_EOL;
        } else {
            echo $this->usageHelp();
        }

    }

    /**
     * Retrieve Usage Help Message
     *
     */
    public function usageHelp()
    {
        return <<<USAGE

Usage:  php -f fix_attributes -- [options]

    --products all              Fix all products
    --products <product_ids>    Fix products by ids
    help                        This help

    <product_ids>               Comma separated id's of products

USAGE;
    }
}

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

If you named the file the same way as we did it, you can run it from your Magento installation root folder by typing this:

php shell/fix_attributes.php

Using script for such work is really helpful. The unnecessary attribute values can be removed for the specified product IDs or for the whole database. Moreover, don’t forget to reindex attributes, clean the cache after running the script and make the backups before running it.

We hope you will find this Magento issue and our solution will be useful for you.

Read more:

Clients

Smart Brands Choose Us.

From Mark Cuban and Sir Richard Branson backed startups, to Inc 500 US Fastest Growing Companies and Global Brands choose us to deliver and support unparalleled eСommerce experience for their customers.