CODEWITHSUNDEEP
magentoattributes
komodosp
komodosp

Reputation: 3616

Magento - How to clean up attributes not in attribute set

I am taking over a website from another developer, and have found the setup of attributes to be a bit of a mess.

Specifically, products have attributes that aren't associated with the relevant attribute sets.

If you take wine with an attribute set "Wines", one of whose attributes is "Grape Variety".

But I also have "Beers" with a completely different attribute set, but somehow one of my beers has a Grape Variety.

It's not assigned to the Beers attribute set, it doesn't show up in the back end for this product, (so I can't edit it) but if I look in the database it's there (in catalog_product_entity_* and catalog_product_index_eav), furthermore when I do an export it's there too, and if someone searches for "Merlot", they are coming up with this beer. There are hundreds of products like this.

What is the best way of removing all attributes from products that are not within their assigned attribute sets?

I could figure it out in SQL I'm sure, but that's not the best way of doing things as I'd be afraid of missing something and screwing up the products altogether.

Upvotes: 3

Views: 2648

Answers (2)

komodosp
komodosp

Reputation: 3616

This is what I ended up doing. A few weeks later and it doesn't seem to have caused any issues yet:

CREATE TABLE catalog_product_entity_int_old LIKE catalog_product_entity_int;
INSERT INTO catalog_product_entity_int_old SELECT * FROM catalog_product_entity_int;

DELETE FROM catalog_product_entity_int 
    WHERE value_id IN 
        (SELECT cpei.value_id 
            FROM catalog_product_entity_int_old cpei 
            WHERE cpei.attribute_id NOT IN 
                (SELECT eea.attribute_id 
                    FROM eav_entity_attribute eea 
                        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id 
                    WHERE cpe.entity_id = cpei.entity_id) 
        ORDER BY cpei.entity_id)

and

DELETE FROM catalog_product_index_eav WHERE
attribute_id NOT IN (
    (SELECT eea.attribute_id 
    FROM eav_entity_attribute eea 
        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id 
    WHERE cpe.entity_id = catalog_product_index_eav .entity_id) 
);

Then regenerate the indices.

Upvotes: 5

Phil Birnie
Phil Birnie

Reputation: 1134

I would definitely use Magmi to clean this up:

First, do a product export from within Magento (System -> Import/Export -> Profiles) and choose "Export All Products." In the resulting CSV, you will have columns for each attribute and you can remove any irrelevant attribute values for each product. This will allow you to bypass the backend where attributes are set for a particular product, but not in the product's attribute set.

Take a good look at the Magmi Wiki, but a few quick tips: Magmi only imports the columns you specify, so you can safely remove most of the columns when you perform your import. For example, I always make sure to remove the image columns if I'm not importing images (or you may lose all of your images). Also, be sure to do a database backup before running your import in case something goes wrong.

Upvotes: 0

Related Questions