Missing categories in Manage Articles, Frontend and Manage Categories fine

Question

I've been banging my head over this a few hours yesterday and seem not get to an solution to this problem.

I imported over 600 products and about 120 categories and other data using the Magento OScommerce Migration Tool, so far all went fine. When I call "Catalog" -> "Manage Categories" in the backend all categories with its subcategories are complete present. Also in the frontend everything is fine so far.

When I go to "Catalog" -> "Manage Articles" to assign a product to a category (using the category tree), I just see about 60% of all categories, 40% just do not show up. When I create a new category it does not show up in the "Manage articles" category tree.

All categories are set to "active" and are "anchored", set to be included in the navigation menu, Root category is assigned properly to the store (I guess otherwise I wouldn't see any category at all).

Basically I checked everything that is described on this page: http://www.alittleofboth.com/2013/08/categories-in-magento-store-will-not-display/

I an other post on the Magentocommerce-Board I found an thread where one says, the problem could possibly be missing "url keys" of category entries. But most (not all) of my categories have an url key and even a few of those not showing in the "Manage Articles" have an url key. I tried moving an category to another position and moving it back to the old position just to get that url key set up. But it does not help at all. Stil not showing up in the articles category assignment tree.

I have no idea how to get this issue fixed, in the database or even programmatically. It is confusing to me that in the frontend as well as in the "Manage categories" all entries and levels are complete but not within the "Manage Articles" category tree.

I am using Magento 1.7.0.2.

Any help or hints is greatly appreciated. Thanks for your time in advance!

Answer 1

Just in case someone else has the same problem:

After a bit (more) digging in the Magento database I found out that there were inconsistencies in the level and number of children of the missed categories in question. I assume this problem results from the Magento OScommerce Migration Tool, maybe it does not update the levels and counts of children of the categories properly. Like said, just an assumption!

Basically 2 SQL queries solved the problem:

Update level column:

UPDATE catalog_category_entity 
SET 
    level = (SELECT LENGTH(path)-LENGTH(REPLACE(path,'/','')) AS tmpl 
FROM (
    SELECT * 
    FROM catalog_category_entity) AS table1
    WHERE catalog_category_entity.entity_id = table1.entity_id);

And to update children_count column:

UPDATE catalog_category_entity SET children_count = (  
    SELECT COUNT( * )
    FROM (
        SELECT *
        FROM catalog_category_entity
    ) AS table2
    WHERE path LIKE CONCAT( catalog_category_entity.path, "/%" )
)

After that I re-indexed everything and the issue was fixed!

I found that solution in an article that can be found here:

http://inchoo.net/ecommerce/magento/solving-problems-with-category-tree-database-information/