Beats me why this isn’t an option in Magento core back-end administration already. I have been using the core patch for Magento Dataflow which allows me to import categories with my products similar to the way Yahoo! eCommerce lets you do it. It makes things easy for importing but you end up with the categories appearing in the order they were specified in your .CSV file you imported, and not alphabetically as most customers would expect when browsing your product catalog from the front-end.
So, rather than manually attempting to reorder 68 categories across 3 levels one-by-one using the tools provided in the Magento back-end, I decided to write a simple MySQL script that would do it all for me automatically. Here it is, for anyone else who may be running into this problem.
DISCLAIMER: This script is provided as-is with no warranty or support. It works for me on the latest version of Magento [as of this writing] which is v184.108.40.206, but not guaranteed to work forever. As always, it is strongly recommended to backup first and test this in a development environment before you attempt to apply it to your live server. You alone are responsible for any damages it may cause in your hands. Use at your own risk.
# adjusted order of categories SET # init vars, set defaults @pos = 0, @last_parent = 0, @last_level = 0 ; DROP TABLE IF EXISTS cce_adjusted; CREATE TEMPORARY TABLE cce_adjusted # output to temp table for review before commit SELECT a.entity_id, a.level, a.parent_id, a.value, a.position, @pos := (IF((@last_parent != a.parent_id) OR (@last_level != a.level), 0, @pos) + 1) `new_position`, @last_level `last_level`, @last_parent `last_parent`, @last_level := CAST(a.level AS UNSIGNED) `new_last_level`, @last_parent := CAST(a.parent_id AS UNSIGNED) `new_last_parent` FROM ( SELECT cce.entity_id, cce.level, cce.parent_id, ccev.value, cce.position FROM catalog_category_entity cce INNER JOIN catalog_category_entity_varchar ccev ON cce.entity_id = ccev.entity_id AND ccev.store_id = 0 # Root Store AND ccev.attribute_id = 31 # Category Name ORDER BY cce.level, cce.parent_id, ccev.value ) a ; SELECT * FROM cce_adjusted; /* # commit changes UPDATE catalog_category_entity cce INNER JOIN cce_adjusted a ON cce.entity_id = a.entity_id SET cce.position = a.new_position ; */ /* # current order of categories SELECT cce.entity_id, cce.level, cce.position, cce.parent_id, ccev.value, 1 `new_position` FROM catalog_category_entity cce INNER JOIN catalog_category_entity_varchar ccev ON cce.entity_id = ccev.entity_id AND ccev.store_id = 0 # Root Store AND ccev.attribute_id = 31 # Category Name ORDER BY cce.level, IF(cce.level<3, cce.position, cce.parent_id), cce.position */
I like to save it as a .sql file and execute it in a MySQL GUI like SQLYog whenever I need, but I suppose it would also work from the CLI if you were applying it to your live server via SSH. The first section previews the changes. The middle section saves changes, and is commented out to prevent accidental changes. You would just uncomment the middle part to make it work. The last section that is commented-out is just for debugging, in case you need to make any changes to this script to get it to work with a customized/non-standard Magento configuration. Also, remember to reset your cache if you have it enabled after you apply this.
Drop me a comment if this script saved you time or you found it useful in any way. Maybe link at it if you think others may also find it useful. It's all that I ask! Keep the open-source spirit alive. Peace.