Why do child categories incorrectly inherit custom design from their parent when "Use Flat Catalog Category" is enabled?

Question

On Magento 1.9.2.4, when "Use Flat Catalog Category" is set to "Yes", a child category will sometimes inherit the settings from its parent category, even if the dropdown for "Use Parent Category Settings" under "Custom Design" says "No".

This only happens in some categories, even though all categories appear to have the same setting for "Use Parent Category Settings".

Further investigation reveals that these categories that were incorrectly inheriting their parents' design actually did NOT have a row for their custom_use_parent_settings attribute in the catalog_category_entity_int table. This causes the value in the catalog_category_flat_store_1 to be NULL.

Even though this value is missing, when "Use Flat Catalog Category" is set to No, the categories behave on the front-end as the admin settings would indicate, and do not inherit the parents' design settings.

I tried setting the default_value of the custom_use_parent_settings attribute in the eav_attribute table to 0, and re-indexed, however, the catalog_category_flat_store_1 table still shows a NULL for this field.

Note: These categories were imported with a script, and not created manually. Because the default_value of this attribute is NULL in the eav_attribute table, it makes sense that there is no record for them in the catalog_category_entity_int table. Had they been created through the admin panel, the value would be 0, as this is the default for the select element. Re-saving a category from admin fixes the issue because the default of the dropdown is "No" or 0, but this isn't an option when you have 1000+ categories.

Answer 1

I'm sure there is a more pragmatic solution for this issue, and I'm hoping that someone else will provide one, but I was strapped for time so I ended up writing an enormous INSERT-SELECT SQL query to fix my existing categories.

It works by finding all the categories that don't yet have a row for this attribute in the catalog_category_entity_int table, and inserts a 0 as the value so that it doesn't get put in the catalog_category_flat_store_1 table as a NULL value. This seems to remedy the incorrect inheritance issue. I made sure to use entity and attribute codes instead of IDs, so you should be able to run this same query if your store has this issue.

Of course, you must refresh the "Category Flat Data" index after running the query, so that the new values get put in the catalog_category_flat_store_1 table.

INSERT INTO `catalog_category_entity_int` 
            (`entity_type_id`, 
             `attribute_id`, 
             `entity_id`, 
             `value`) 
(SELECT `entity_type_ids`.`entity_type_id`, 
        `attribute_ids`.`attribute_id`, 
        `entity_ids`.`entity_id`, 
        0 AS `value` 
 FROM   (SELECT entity_id 
         FROM   catalog_category_entity 
         WHERE  entity_id NOT IN (SELECT entity_id 
                                      FROM   catalog_category_entity_int 
                                      WHERE  attribute_id = (SELECT attribute_id 
                                                         FROM   eav_attribute 
                                                         WHERE  entity_type_id = (SELECT entity_type_id
                                                                                  FROM   eav_entity_type
                                                                                  WHERE  entity_type_code = "catalog_category")
                                                                AND attribute_code = "custom_use_parent_settings"))) AS entity_ids,
        (SELECT entity_type_id 
         FROM   eav_entity_type 
         WHERE  entity_type_code = "catalog_category") AS entity_type_ids, 
        (SELECT attribute_id 
         FROM   eav_attribute 
         WHERE  entity_type_id = (SELECT entity_type_id 
                                      FROM   eav_entity_type 
                                      WHERE  entity_type_code = "catalog_category") 
                AND attribute_code = "custom_use_parent_settings") AS attribute_ids)