3

After migrating my Magento DB to 1.14, configurable products are not showning anymore in product category list.

On Indexing, the child products are indexed into catalog_product_index_price, but not the configurable product.

The reason why is that Magento Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption() delete product where parent_id is NULL before moving it from catalog_product_index_price_final_tmp to the catalog_product_index_price table

Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable (line 234)

        $select = $write->select()
        ->from($table)
        ->join(
            array('e' => $this->getTable('catalog/product')),
            'e.entity_id = i.entity_id',
            array())
        ->joinLeft(
            array('coa' => $coaTable),
            'coa.parent_id = i.entity_id',
            array())
        ->where('e.type_id = ?', $this->getTypeId())
        ->where('coa.parent_id IS NULL');

    $query = $select->deleteFromSelect('i');
    $write->query($query);

If I comment out this code, configurable products would show up on the frontend after the price re-index.

Any body knows why this configurable products cleanup was added in price reindex ?

2

In my case it was due because the configurable attribute used in the configurable product didn't have a backend_type equal to int but was a varchar. So the attribute was not found in the catalog_product_entity_int table which is needed in the class Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable to generate the price index.

  • You'd be able to find these attributes by running SELECT t.* FROM eav_attribute t WHERE backend_type != 'int' and entity_type_id = 4 AND attribute_id IN (SELECT attribute_id FROM catalog_product_super_attribute) – Peter Jaap Blaakmeer Jul 3 at 9:36
0

I always feel this is a core bug. Configurable attributes should be an INT type. All other data types will leads to chaos.

It is always better to keep all your select attributes in INT. I believe Magento requires this to properly index all those attributes. Possible issues which you will face if you keep you select attribute other than INT are well explained in the above link. Key points are noting down.

  • Suppose you have a select attribute which backend type is VARCHAR. Everything is fine. You can assign this attribute to an attribute set and all products are saving all it’s values as we need. Now you came to a situation in which you want to update this attribute via backend. Of course, you can do this via admin side, Catalog > Attributes > Manage Attributes > [Select Your Attribute] > [Do all changes] > SAVE. Bingo attribute is updated, but if you check your database an important hack is done without your knowledge which is backend type of your attribute is now INT and not VARCHAR. This is definitely going to make problems in future.

  • Suppose you have a configurable product attribute which backend type is VARCHAR. You did a massive product import to your system. Now configurable product won’t show in category and even in search page. There is no problem with other simple products which uses same attribute, but configurable product won’t show in the assigned category pages.. Most probably this is an indexing issue.

Instead if all of your configurable attributes are using backend type as INT, then there is no problem at all.

  • 1
    Both those things happened to me today. The second one is, indeeed, related to indexing, because from 1.9.0.0 on, the products with a non-int configurable attribute won't make it into the price index. – Jan Tomka Nov 2 '16 at 6:04
0

Good one, @Sylvain! While configurable attributes can only be of type "int", other types seem to have been silently accepted before version CE1.9/EE1.14.

Just for reference, say your attribute is incorrect type varchar, id 125, empty/default option id is 137. You'd fix your database with the following (don't copy/paste, you'll need to update for your scenario):

insert into catalog_product_entity_int
  select null value_id, entity_type_id, attribute_id, store_id,
  entity_id, if(ifnull(value, '') = '', 137, value) value
  from catalog_product_entity_varchar where attribute_id = 125;
update eav_attribute set backend_type = 'int' where attribute_id = 125;
delete from catalog_product_entity_varchar where attribute_id = 125;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.