EAV Attribute Values Stored in Database

databaseeavvarchar

I have an attribute in use on Magento where the data has been split between
catalog_product_entity_varchar and catalog_product_entity_decimal due to the datatype being changed in the database.

The correct data is stored in the majority in catalog_product_entity_varchar, however products added whilst the datatype was changed to decimal are always referencing the catalog_product_entity_decimal table for this attribute, even when that entry is deleted and then a new value saved from inside magento.

This leads me to believe there is a setting somewhere that tells magento which table to use for each attribute, per product. Does anyone know where this is?

Best Answer

Whenever you save attribute from the admin panel, Magento will run validation on it and it will change your backend type based on input type. You can see that in getBackendTypeByInput method inside Mage_Adminhtml_Catalog_Product_AttributeController class.

   /**
     * Detect backend storage type using frontend input type
     *
     * @return string backend_type field value
     * @param string $type frontend_input field value
     */
    public function getBackendTypeByInput($type)
    {
        $field = null;
        switch ($type) {
            case 'text':
            case 'gallery':
            case 'media_image':
            case 'multiselect':
                $field = 'varchar';
                break;

            case 'image':
            case 'textarea':
                $field = 'text';
                break;

            case 'date':
                $field = 'datetime';
                break;

            case 'select':
            case 'boolean':
                $field = 'int';
                break;

            case 'price':
                $field = 'decimal';
                break;
        }

        return $field;
    }

You can use this as a guideline when adding new attributes via install script. If you are adding attribute via admin you don't have to worry about that. Of course, if you modify backend or frontend type directly from DB, Magento will validate and 'fix' the attribute on next save.

In other words you should never, ever change attribute properties directly from DB. Especially if the attribute already has some values saved. Doing this will result in attribute values being written in different entity type tables, which is what happened in your case. This will result in various issues, e.g. attribute disappearing from layered navigation, not searchable or filterable, cannot be saved, etc.

Fix for this is simple. First you need to determine what your backend/input types are. Then you need to make sure that the combination is valid using the code above and that it won't be changed in the future by manual editing.

Let's assume you want decimal/price. This means that your values should be saved in catalog_product_entity_decimal table. You will have to check all the other catalog_product_entity_* tables and remove all entries associated with your attribute_id. If you need to preserve the data you can also export rows before you delete them (without value_id) and import them in catalog_product_entity_decimal table.

This should fix your attribute. Remember to reindex after this is done.

Related Solutions

Magento – save to multiple tables

There are multiple ways to save data to two tables at a time.

If you've registered the resource (table-backed) model in Magento's ORM you should be able to do something to the effect of:

$table1 = Mage::getModel('yourmodel/table1');
$table1->setData(array('some'=>'stuff','in'=>'here'));
$table1->save();

Wash, rinse, repeat for the second table - assuming you have a column called parent_id:

$table2 = Mage::getModel('yourmodel/table2');
$table2->setParentId($table1->getId());
$table2->setData($table1->getData());
$table2->save();

This accomplishes your goal of having a table2 related to table1 by the primary key.

Another thought is that if your RDBMS (MySQL?) has the concept of a trigger, you could set up a trigger.

Magento – How to completely delete EAV attribute and all its data from the database

If you got orphan attribute values it means that there is something wrong with your database.
For example the table catalog_category_int has a constraint on the attribute_id column.

CONSTRAINT `FK_CAT_CTGR_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,

This means that if the attribute is deleted, all the values for the attribute from the table catalog_category_int will be deleted.
It's the same for the rest of the tables (catalog_category_*).

From experience I saw that usually constraints are lost during a migration process. You export the database but without the constraints. If this is the case then remember in the future to use System->Tools->Backupto create database dumps for migration.

[EDIT]
Here is the code I've used. I've created a simple extension that adds an attribute to the category entity, I edited a category and filled in a value for that attribute, then deleted the attribute through an upgrade script.
Here are the extension files:

app/etc/module/Easylife_Attr.xml - declaration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Attr>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Catalog />
            </depends>
        </Easylife_Attr>
    </modules>
</config>

app/code/local/Easylife/Attr/etc/config.xml - the configuration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Attr>
            <version>0.0.1</version>
        </Easylife_Attr>
    </modules>
    <global>
        <resources>
            <easylife_attr_setup>
                <setup>
                    <module>Easylife_Attr</module>
                    <class>Mage_Catalog_Model_Resource_Setup</class>
                </setup>
            </easylife_attr_setup>
        </resources>
    </global>
</config>

app/code/local/Easylife/Attr/sql/easylife_attr_setup/install-0.0.1.php - the install script

<?php

$this->addAttribute('catalog_category', 'dummy', array(
    'group'         => 'General',
    'input'         => 'textarea',
    'type'          => 'text', //can be any type
    'label'         => 'Custom attribute',
    'backend'       => '',
    'visible'       => true,
    'required'      => false,
    'visible_on_front' => true,
    'global'        => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL
));

Now add a value for this dummy attribute for a category.
You should see it in the table catalog_category_entity_text.

After that create an upgrade script to remove the attribute. app/code/local/Easylife/Attr/sql/easylife_attr_setup/upgrade-0.0.1-0.0.2.php

<?php
$this->removeAttribute('catalog_category', 'dummy');

and change in config.xml the version from 0.0.1 to 0.0.2.

Refresh any page and look in the same table catalog_category_entity_text. The value that was added for the dummy attribute is gone. (at least for me).

Best Answer

Related Solutions

Magento – save to multiple tables

Magento – How to completely delete EAV attribute and all its data from the database

Related Topic