I have this tree of categories in magento with main>subcat>subcat1. 3 levels. I want to sort the first two levels with custom order and the subcategories of the second level alphabetically. I found catalog_category_entity table and I am sorting each categories one by one. The problem is that I have 900 categories and this is a waste of time. I don't know about databases but is there some queries: 1)to select main categories so I can order them custom 2)to select subcategories for same reason 3)to select the third level of categories and sort the automatically in alphabetical order

1 Answer 1


Run these queries on the database to achieve your goals.

Query to select main categories:

SELECT * FROM `catalog_category_entity` WHERE `level` = 1;

Query to select subcategories:

SELECT * FROM `catalog_category_entity` WHERE `level` = 2;

To sort alphabetically the third level categories:

/* define a variable */
SET @rownum=0;

/* Pay attention to the attribute_id which must correspond to the Name attribute of the category */
update `catalog_category_entity` cat
join (
    SELECT cce.entity_id
    FROM `catalog_category_entity` cce 
    JOIN `catalog_category_entity_varchar` ccev ON cce.entity_id = ccev.entity_id
    WHERE cce.`level` = 3 and ccev.`attribute_id` = 41 and ccev.`store_id` = 0
    ORDER BY ccev.`value`
) r
on r.entity_id = cat.entity_id
set cat.`position` = (@rownum := @rownum + 1);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.