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`;