Today one of our clients reported issue that Magento category tree broken in admin especially when they were trying to edit existing products, after looking at the code where things are coming from then we went into catalog_category_entity table and found that children_count for all records were set to zero
We tried running the following query first to get the children_count for just one category
SELECT COUNT(*) FROM catalog_category_entity WHERE path LIKE '1/68/%';
In the above example 68 is our category id so don’t forget to replace with your category id when you run the above query.
To make sure that it resolves the problem, we run the below query to update only one category record i.e. 68
UPDATE catalog_category_entity SET children_count = (SELECT COUNT(*) FROM (select * from catalog_category_entity WHERE path LIKE '1/68/%') as category_table) WHERE path='1/68'
After running the above update query, we found the category id 68 appeared fine in admin panel in categories tab, viola!
To fix the problem completely, we run the following query which fixed children_count for all categories and finally in admin panel all categories were appearing fine.
UPDATE catalog_category_entity SET children_count = (SELECT COUNT(*) FROM (SELECT * FROM catalog_category_entity) AS category_table WHERE path LIKE CONCAT(catalog_category_entity.path,"/%"));
Hope this article helped you in some way. Please leave us your comment and let us know what do you think? Thanks.
