Magento EAV: how to hard delete an attribute value?

Question

Let's ask the question clearly before entering into the details:

---

Is there a way of hard deleting an attribute value from a product?

By hard I mean, removing the row from the database and not only setting the value to null or empty.

---

Now, the details:

I'm currently confronted to a problem on Magento.

Many product in my store have user defined attributes that are not related to the product. For example, let's say we have a bag product, among other products like t-shirts,dresses,pants,etc.:

Our bag product has only configurable 1 attribute: color.

Our T-Shirt product has 2 configurable attributes: color and tshirt_size.

Our Dress product has 2 configurable attributes: color and dress_size

My current problem is that when I retrieve my Bag product from the database, the attributes stored in my product are: color, tshirt_size and dress_size.

I suspect that it is because on our store you can change the attribute set of a product (thanks to the FlagBit ChangeAttributeSet community extension).

I think that the bag product has been created with a specific attribute set, then someone changed the attribute set to another, and another again. That would have lead the product to get ALL the attributes from ALL the attribute sets it has been changed to. But that's just a though, and it could be another reason. I'm actually currently not looking for the specific reason that led my product to have unrelated attributes.

I today want to reset my products. By reset I mean removing the attributes unrelated to my product. In the case of my bag, that means removing the tshirt_size and dress_size attributes. And by removing I mean deleting forever these attributes from the object.

My problem is that, I can't find how to do it. I tried setting the attributes to NULL and saving the product, but when I query again the product, I still get the attribute in the datas, with a null value. I don't want the attribute value to be null, I want the attribute to not exist.

Here is a sample of code that explains a bit what I tried:

$product = Mage::getModel('catalog/product')->load(1234); //Let's assume that my bag product ID is 1234

Mage::log($product->getData());

/* This last line dump all my products datas and contains among other things:

[...]=>...
[color]=>3
[tshirt_size]=>34
[dress_size]=>45
[...]=>...

*/

If I do a:

$product->setData('tshirt_size',null);
$product->setData('dress_size',null);
$product->save();

then again:

$product = Mage::getModel('catalog/product')->load(1234);
 Mage::log($product->getData());

    /* I get:

    [...]=>...
    [color]=>3
    [tshirt_size]=>null
    [dress_size]=>null
    [...]=>...

    */

I don't want that. I want:

/*

[...]=>...
[color]=>3
[...]=>...

*/

The fact that I still have the entries in my array mean that the actual rows in the database are not deleted. I want the rows to be deleted.

Even more frustrating, when I do a:

$product->getAttributes();

It returns me all the attributes that can be set to my product without the unrelated attributes -which makes sense because the unrelated attributes are not in the attribute set of my product.

So, again, the question is:

Is there a way of hard deleting an attribute value from a product?

By hard I mean, removing the row from the database and not only setting the value to null or empty.

Thanks for your help!

Hugues.

FYI: I'm using Magento 1.6.1.0 (but it does not really change anything)

Answer 1

I finally had to tackle this, and although @vBuck was close, I always was able to query the old data after the switch in the attribute set and if I switched back I would see the old data when it shouldn't have been there. Today, I have the same need to ensure the random data is removed. Here is how I approached this topic.

In order to programmatically delete attributes when you switch from one set to another you must first look at all the entries for the new set, then get a list of all attributes, check against that list and (this is the important part) remove it from the correct entity table. For example, I want to get rid of a value of 500 GB that is in the sample Magento data when I switch Western Digital 500GB HD - 7200RPM from Hard Drive to Shoes. In order to do that I must remove the entry in the catalog_product_entity_text table it's stored at. This means that I must find that entry where it's equal to the product id and then delete it from it's entity type table.

I was able to do this, and was not able to find the stray data after. This was confirmed by both a DB search and switching back to the old attribute set and looking for the data. you can find it all here

https://gist.github.com/jeremyBass/6581038#file-attribute-clean-switch-md

TESTED in CE 1.7.0.2, 1.8.0.0