Missing default store value of an attribute

Question

I have a customer product attribute (id = 283) that stores data in catalog_product_entity_varchar

I noticed that sometimes when I load a product, or a collection, this attribute 283 and its value is missing in the product data.

I have identified it is because there is no value for the default store (0), it fails some SQL query, here an extract of the database:

value_id    entity_type_id  attribute_id    store_id    entity_id   value
30668852    4               283             2           1581        abc
20843831    4               283             0           45951       NULL
20843868    4               283             2           45951       def
21131745    4               283             3           45951       NULL
21138268    4               283             4           45951       NULL

For the product 45951, I have a value for each store, including the default one. However for the product 1581, it is only defined for the store 2.

I realized that the products missing the NULL value for the default store were created before the creation of the custom attribute.

Is there a proper way to fix catalog_product_entity_varchar (by creating the NULL values missing)?

I tried to reindex without success.

The way I load the products:

$products = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('entity_id', array('in' => $ids));

Answer 1

It depends how you load the product, you will get all the attributes when you load it like Mage::getModel('catalog/product')->load($id);

If you want to use collections and still load some specific attributes but not all (performance) you can do it using addAttributeToSelect()

$collections = Mage::getModel('catalog/product')
   ->getCollection()
   ->addAttributeToSelect(array('name', 'price'))
   ->addAttributeToFilter('price', array('eq' => 10.00))
   ->load();

If you can try to avoid:

->addAttributeToSelect('*')

Answer 2

There is problem in the way how Magento loads the attribute values into the product collection. The store values are joined to the default values, unfortunately if the default value is missing then the store value is ignored.

I think it is wrong. Moreover this phenomen appears only if flat index is not available. If flat index works then the store values are loaded into the product collection properly no matters whether the default values exist or not.

I solved it by extending class Mage_Catalog_Model_Resource_Collection_Abstract in app/code/core/Mage/Catalog/Model/Resource/Collection/Abstract.php about these methods

/**
 * Load attributes into loaded entities
 *
 * @throws Exception
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
public function _loadAttributes($printQuery = false, $logQuery = false)
{
    if (empty($this->_items) || empty($this->_itemsById) || empty($this->_selectAttributes)) {
        return $this;
    }

    $entity = $this->getEntity();

    $tableAttributes = array();
    $attributeTypes  = array();
    foreach ($this->_selectAttributes as $attributeCode => $attributeId) {
        if (!$attributeId) {
            continue;
        }
        $attribute = Mage::getSingleton('eav/config')->getCollectionAttribute($entity->getType(), $attributeCode);
        if ($attribute && !$attribute->isStatic()) {
            $tableAttributes[$attribute->getBackendTable()][] = $attributeId;
            if (!isset($attributeTypes[$attribute->getBackendTable()])) {
                $attributeTypes[$attribute->getBackendTable()] = $attribute->getBackendType();
            }
        }
    }

    $selects = array();
    foreach ($tableAttributes as $table=>$attributes) {
        $select = $this->_getLoadAttributesSelect($table, $attributes);
        $selects[$attributeTypes[$table]][] = $this->_addLoadAttributesSelectValues(
            $select,
            $table,
            $attributeTypes[$table]
        );
        // HACK
        $selectStore = $this->_getLoadAttributesSelectStoreOnlyValues($table, $attributes);
        if ($selectStore) {
            $selects[$attributeTypes[$table]][] = $this->_addLoadAttributesSelectValues(
                $selectStore,
                $table,
                $attributeTypes[$table]
            );
        }
        // END HACK
    }
    $selectGroups = Mage::getResourceHelper('eav')->getLoadAttributesSelectGroups($selects);
    foreach ($selectGroups as $selects) {
        if (!empty($selects)) {
            try {
                $select = implode(' UNION ALL ', $selects);
                $values = $this->getConnection()->fetchAll($select);
            } catch (Exception $e) {
                Mage::printException($e, $select);
                $this->printLogQuery(true, true, $select);
                throw $e;
            }

            foreach ($values as $value) {
                $this->_setItemAttributeValue($value);
            }
        }
    }

    return $this;
}

protected function _getLoadAttributesSelectStoreOnlyValues($table, $attributeIds = array())
{
    if (empty($attributeIds)) {
        $attributeIds = $this->_selectAttributes;
    }
    $storeId = $this->getStoreId();

    if ($storeId) {

        $adapter        = $this->getConnection();
        $entityIdField  = $this->getEntity()->getEntityIdField();
        $joinCondition  = array(
            't_s.attribute_id = t_d.attribute_id',
            't_s.entity_id = t_d.entity_id',
            't_s.store_id = 0'
        );

        $select = $adapter->select()
            ->from(array('t_s' => $table), array($entityIdField, 'attribute_id'))
            ->joinLeft(
                array('t_d' => $table),
                implode(' AND ', $joinCondition),
                array())
            ->where('t_s.entity_type_id = ?', $this->getEntity()->getTypeId())
            ->where("t_s.{$entityIdField} IN (?)", array_keys($this->_itemsById))
            ->where('t_s.attribute_id IN (?)', $attributeIds)
            ->where('t_s.store_id = ?', $storeId)
            ->where('t_d.entity_type_id IS NULL');

    } else {
        $select = null;
    }

    return $select;
}

Method _getLoadAttributesSelectStoreOnlyValues builds query for loading the store values if some relevant default value does not exist (method comes from original _getLoadAttributesSelect)

Method _loadAttributes comes from parent class and it extends the "main query" for loading the attribute values about adding query builded by _getLoadAttributesSelectStoreOnlyValues.

Of course, move class to the local code pool (app/code/local/Mage/Catalog/Model/Resource/Collection/Abstract.php) before making some changes.