Fix and/or prevent wrong value of `children_count` in `catalog_category_entity`

[loekvangool]

Steps to reproduce (*)

Steps are described at https://www.sonassi.com/blog/magento-kb/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()).

Fix

A SQL fix is available (from that same blog):

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;

This query seems to work, but it's after-the-fact of course and we would have to build this into an Indexer or Cache clearing button or something like that for it to work.

The impact (afaik) is:

1. Admin Category tree showing that a category has children, while it has none (impact limited)
2. Admin Category tree showing no children when there are children (bigger impact). This is because the value of children_count may even become negative.

I'm open to a deeper fix but it seems we can stop negative values, and perhaps we can do something to remove the has-children icon in case children = [] (fixes point 1).

[loekvangool]

We could prevent negative values like so:

app/code/core/Mage/Catalog/Model/Resource/Category.php:

line 137: $data = array('children_count' => new Zend_Db_Expr('MAX(0, children_count - ' . $childDecrease) . ')');
...
line 834: array('children_count' => new Zend_Db_Expr('MAX(0, children_count - ' . $childrenCount) . ')'),

However, it would not prevent a faulty 0 from forming.

[loekvangool]

I can confirm that above SQL sequence fixes the issue (after the fact, of course).

[addison74]

PR?