Magento category children count fix

The children_count field in Magento can sometimes become inaccurate if you have created a category via the PHP API and set an entity_id whilst doing so. It is due to part of the after_save mechanism which overrides the children_count parameter (even if explicitly set via $_category->setChildrenCount()).

For the majority of the admin area, an inaccurate children count won’t normally pose an issue, however, upon using the AJAX category tree (Eg. in product edit, promotional rules etc.) – any category with an inaccurate children count may not display the expand icon.

It can quickly and easily be reset, albeit with a bit of quick and dirty SQL.

CREATE TABLE catalog_category_entity_tmp LIKE catalog_category_entity;
INSERT INTO catalog_category_entity_tmp SELECT * FROM catalog_category_entity;

UPDATE catalog_category_entity cce
SET children_count =  
(
SELECT count(cce2.entity_id) as children_county
FROM catalog_category_entity_tmp cce2
WHERE PATH LIKE CONCAT(cce.path,'%') 
); 

DROP TABLE catalog_category_entity_tmp;
  • Ashish Zarkar

    Thanks! It really helped me a lot and saved my one day working!

    Really Appreciate:-) Great Article

    Thank You
    Ashish

  • Lucas Moeskops

    Thank you kindly for the script :-)

  • Lucas Moeskops

    Each entry is however also selecting itself as its own child, so I think it should be:

    CREATE TABLE catalog_category_entity_tmp LIKE catalog_category_entity;
    INSERT INTO catalog_category_entity_tmp SELECT * FROM catalog_category_entity;
    UPDATE catalog_category_entity cce
    SET children_count =
    (
    SELECT count(cce2.entity_id) – 1 as children_county
    FROM catalog_category_entity_tmp cce2
    WHERE PATH LIKE CONCAT(cce.path,’%’)
    );
    DROP TABLE catalog_category_entity_tmp;

  • http://shop.itmanx.com Christian

    You might want to check tmp table doesnt exist first

    DROP TABLE IF EXISTS `catalog_category_entity_tmp`;
    CREATE TABLE `catalog_category_entity_tmp` LIKE `catalog_category_entity`;
    INSERT INTO `catalog_category_entity_tmp` SELECT * FROM `catalog_category_entity`;
    UPDATE `catalog_category_entity` cce
    SET children_count =
    (
    SELECT count(entity_id)-1
    FROM `catalog_category_entity_tmp`
    WHERE PATH LIKE CONCAT(cce.path,’%’)
    );
    DROP TABLE `catalog_category_entity_tmp`;

  • Chirag Nandaniya

    thanks for this stuff

  • Andrew Male

    After running this we found that it added a children_count of 1 to categories that had no children because the SQL part:

    LIKE CONCAT(cce.path,’%’)

    is finding the parent as well as the children e.g. it returned 3 instead of 2 as below:

    1/2/508/544 – parent

    1/2/508/544/123 – child

    1/2/508/544/124 – child

    We amended the SQL slightly to subtract 1 from the count:

    CREATE TABLE catalog_category_entity_tmp LIKE catalog_category_entity;
    INSERT INTO catalog_category_entity_tmp SELECT * FROM catalog_category_entity;

    UPDATE catalog_category_entity cce
    SET children_count =
    (
    SELECT count(cce2.entity_id)-1 as children_county
    FROM catalog_category_entity_tmp cce2
    WHERE PATH LIKE CONCAT(cce.path,’%’)
    );

    DROP TABLE catalog_category_entity_tmp;

  • Cristiano Casciotti

    In the where clause, it is better to add a slash before the percent sign:

    SELECT count(cce2.entity_id) as children_county
    FROM catalog_category_entity_tmp cce2
    WHERE PATH LIKE CONCAT(cce.path,’/%’)

    So you don’t accidentally retrieve any subcategory that starts with the given category id. So if you have a category ID (ie. 801), without any slash the query can also return categories with an ID like 8011, 8012 and so on. If you do that you don’t need to subtract 1 in the SELECT COUNT instruction.