Category Flat Data problem: description is missing in flat table without indexing error

Question

I'm working on M1 CE 1.7.0.2.

I've enabled Use Flat Catalog Category from backend (catalog/frontend/flat_catalog_category = 1).

Then I reindex php shell/indexer.php --reindexall without any error. When I check indexes in backend: all lines are green.

When I check my categories on frontend: description and category image are not showing.

If I disable the Flat Catalog Category all goes fine back again.

Generally an exception is thrown during reindex of catalog_category_flat but here, nothing.

Is anyone had facing this issue? How can I debugging indexer to ensure he catch all category parameters before saving into catalog_category_flat_store_x db table?

Thank you in advance for your help!

---

After a deep analysis, I've found the code responsible of this situation.

The method in cause is responsible of retrieving attributes values when index process is saving data to flat table:

Mage_Catalog_Model_Resource_Category_Flat::_getAttributeTypeValues().

protected function _getAttributeTypeValues($type, $entityIds, $sid)
{
    $select = $this->_getWriteAdapter()->select()
        ->from(
            array('def' => $this->getTable(array('catalog/category', $type))),
            array('entity_id', 'attribute_id')
        )
        ->joinLeft(
            array('store' => $this->getTable(array('catalog/category', $type))),
            'store.entity_id = def.entity_id AND store.attribute_id = def.attribute_id AND store.store_id = '.$sid,
            array('value' => $this->_getWriteAdapter()->getCheckSql('store.value_id > 0',
                $this->_getWriteAdapter()->quoteIdentifier('store.value'),
                $this->_getWriteAdapter()->quoteIdentifier('def.value'))
            )
        )
        ->where('def.entity_id IN (?)', $entityIds)
        ->where('def.store_id = ?', Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID);
    return $this->_getWriteAdapter()->fetchAll($select);
}

The problem is at line 829:

where('def.store_id = ?', Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID);

This is a $select output example analyzed during my debug:

SELECT def.entity_id, def.attribute_id, IF(store.value_id > 0, store.value, def.value) AS value FROM catalog_category_entity_text AS def LEFT JOIN catalog_category_entity_text AS store ON store.entity_id = def.entity_id AND store.attribute_id = def.attribute_id AND store.store_id = 43 WHERE (def.entity_id IN ('1', '164')) AND (def.store_id = 0)

Look at the end: def.store_id = 0

This condition exclude the description that are saved at store view level. It means category description is indexed only if description is save on default scope.

My categories get no description value at default scope, then receive a value only at store view level.

If I comment the line 829 then reindex, it works like a charm.

Then I thought maybe it was updated in a newer release of Magento CE. I've checked the 1.9.1 and this method hasn't changed.

Is this situation normal? I think it's not.

I hope a Magento Guru will get through here to give his advice.

Cheers.

Answer 1

Then I thought maybe it was updated in a newer release of Magento CE. I've checked the 1.9.1 and this method hasn't changed.

That's (un)fortunately not right. In Magento 1.8.0.0 the mehtod changed to:

/**
 * Return attribute values for given entities and store of specific attribute type
 *
 * @param string $type
 * @param array $entityIds
 * @param integer $storeId
 * @return array
 */
protected function _getAttributeTypeValues($type, $entityIds, $storeId)
{
    $select = $this->_getWriteAdapter()->select()
        ->from(
            array('def' => $this->getTable(array('catalog/category', $type))),
            array('entity_id', 'attribute_id')
        )
        ->joinLeft(
            array('store' => $this->getTable(array('catalog/category', $type))),
            'store.entity_id = def.entity_id AND store.attribute_id = def.attribute_id '
                . 'AND store.store_id = ' . $storeId,
            array('value' => $this->_getWriteAdapter()->getCheckSql(
                'store.value_id > 0',
                $this->_getWriteAdapter()->quoteIdentifier('store.value'),
                $this->_getWriteAdapter()->quoteIdentifier('def.value')
            ))
        )
        ->where('def.entity_id IN (?)', $entityIds)
        ->where('def.store_id IN (?)', array(Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID, $storeId));
    return $this->_getWriteAdapter()->fetchAll($select);
}

See: https://github.com/OpenMage/magento-mirror/blob/magento-1.8/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php#L839-L867