Magento EAV: How to Hard Delete an Attribute Value
Magento’s EAV (Entity-Attribute-Value) model is powerful but can sometimes lead to confusion when dealing with product attributes. In this post, we’ll tackle the challenge of completely removing irrelevant attribute values from a product.
The Problem: Unrelated Attributes in Products
Let’s consider a typical Magento store selling various types of clothing. We have:
- Bag (with an attribute set containing only
color) - T-Shirt (with an attribute set containing
colorandtshirt_size) - Dress (with an attribute set containing
coloranddress_size)
However, due to changing product attributes sets using extensions like FlagBit ChangeAttributeSet, a bag product might end up having unrelated attributes like tshirt_size and dress_size. The issue is that even if we switch back the attribute set, these irrelevant values remain in the database.
Why We Want to Hard Delete Attribute Values
Setting an attribute value to null or empty doesn’t actually remove it from the database. The row remains there, which can lead to bloated and disorganized data over time, especially for larger product catalogs.
In our example, we want to completely delete the tshirt_size and dress_size rows associated with the bag product ID 1234.
Current Attempts That Don’t Work
Attempting to set the attribute values to null:
$product = Mage::getModel('catalog/product')->load(1234);
$product->setData('tshirt_size', null);
$product->setData('dress_size', null);
$product->save();
This only sets the value to null in the database and doesn’t physically remove the row. The data still looks like this:
'tshirt_size' => null,
'dress_size' => null,
The Solution: Delete Attribute Values Directly from Catalog_product_entity_int Table
Since Magento uses the EAV model, we need to directly interact with the database tables involved.
The catalog_product_entity_int table stores all integer attribute values. To delete a specific value for a product:
- First, get the attribute ID using the
eav_attributetable.
$attributeId = Mage::getResourceModel('eav/entity_attribute')
->getAttributeIdByCode('catalog_product', 'tshirt_size');
- Delete the row from the
catalog_product_entity_inttable where the product ID and attribute ID match.
$writeConnection = Mage::getSingleton('core/resource')->getConnection('core_write');
$sql = "DELETE FROM catalog_product_entity_int WHERE entity_id=1234 AND attribute_id=$attributeId";
$writeConnection->query($sql);
Replace tshirt_size with the actual attribute code and 1234 with your product ID.
The Complete Function to Delete Unrelated Attribute Values
Here’s a complete function that deletes all irrelevant attributes from a given product:
public function deleteUnrelatedAttributeValues($productId)
{
$writeConnection = Mage::getSingleton('core/resource')->getConnection('core_write');
// Get the attribute IDs of the current product's set
$attributeSetId = Mage::getModel('catalog/product')->load($productId)->getAttributeSetId();
$attributesInSet = Mage::getResourceModel('eav/entity_attribute')
->getCollection()
->addFieldToFilter('attribute_set_id', $attributeSetId)
->getAllIds();
// Get all attribute values for the product
$sql = "SELECT * FROM catalog_product_entity_int WHERE entity_id=$productId";
$rows = $writeConnection->fetchAll($sql);
foreach ($rows as $row) {
if (!in_array($row['attribute_id'], $attributesInSet)) {
// Delete rows that are not in the current attribute set
$deleteSql = "DELETE FROM catalog_product_entity_int WHERE entity_id=$productId AND attribute_id=" . $row['attribute_id'];
$writeConnection->query($deleteSql);
}
}
return true;
}
This function deletes all attribute values that are not part of the current product’s attribute set.
Conclusion
Hard deleting attribute values in Magento requires direct interaction with the database, specifically the catalog_product_entity_int table. By using the provided function, you can effectively clean up your products by removing irrelevant attributes and optimizing your data structure.
Remember to always backup your data before performing any manual queries on your production environment.
For more comprehensive solutions to Magento’s EAV model challenges, consider visiting Laravel Company for expert insights and best practices.