Adding Child Data to the Product Flat Index

Question

Summary: Is there any action required to pull data from "child products" into configurables on flat tables other than setting global/catalog/product/flat/add_child_data to 1?

App: Magento 1.14.2.4

Action: Reindexing Catalog Product Flat table with n98-Magerun

More detail follows...

In order to pull a configurable products associated simple data into the flat table, I have set the config value of global/catalog/product/flat/add_child_data to '1'.

This configuration value is read by Mage_Catalog_Helper_Product_Flat::isAddChildData(), which itself is used by Enterprise_Catalog_Model_Index_Action_Product_Flat_Refresh::_createTemporaryFlatTable, Enterprise_Catalog_Helper_Product::getFlatColumnsOldDefinition and others. These classes use the predicate to properly generate the structure for the resulting flat table, and the temporary indexing tables.

The problem I'm having is that the Product Refresh Model's _fillTemporaryFlatTable method attempts to insert into the temp table (which now includes child_id) without providing data for that column. This occurs even though that column has a FK constraint on the catalog_product_entity table. Needless to say, the attempted insert fails with a scary-looking SQLSTATE[23000]: Integrity constraint violation message. This is occurring during an n98-Magerun index:reindex catalog_product_flat run.

So far, I've searched DDG and Google for both (and variations on)

"add_child_data"

and

"isaddchilddata" magento

but none of those searches yielded useful results. The closest to useful result I found was this blog post by the Excellence Magento Blog. The following excerpt summarizes my goal:

‘global/catalog/product/flat/add_child_data’ : if this is set to 1, two column are added ‘is_child’ and ‘child_id’. Both these store relationship between configuration, grouped, bundled and its associated simply product.

Here's what I've found so far:

1. When enabling add_child_data, child_id and is_child are added to the flat columns used in the temporary and resultant flat tables
2. However, adding child data does not appear to affect the select statement that reads from various product attribute tables
3. This results in a foreign key constraint violation where the child_id column isn't present in the data which would be "inserted from select"

I'm primarily looking at Enterprise_Catalog_Model_Index_Action_Product_Flat_Refresh::_fillTemporaryFlatTable right now, trying to figure out where in that method (or a previous one, maybe?) the child_id data would be added to the select.

Thanks a bunch for reading. Hope this makes some sort of sense!

Answer 1

So, if you are using attributes for configurable products and you want those attributes to be shown in the flat tables, you need to have the attributes configured so that they have the used_in_product_listing set to TRUE

This will then add the columns to the flat table assuming that you have the flat catalog product options set to Yes in the system config.

Don't forget to re-index, and refresh cache.

Checkout: http://docs.magento.com/m1/ee/user_guide/catalog/attribute-frontend-properties.html