Automatically Sorting Magento Catalog Categories Alphabetically via MySQL

Magento Open Source eCommerce logo 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 v1.2.1.2, 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.

3 thoughts on “Automatically Sorting Magento Catalog Categories Alphabetically via MySQL

  1. Dylan

    On 1.6 I just deleted “AND ccev.attribute_id = 31 # Category Name” from the innerjoin in the first section, and everything worked fine.

  2. Sudeep Talati

    Thanks a ton. I am working on Magento 1.7 and have imported 4500 categories. All these categories have to be ordered which were upto 4 level down. The only thing I will suggest all is that check what is your attribute ID and Store id. As very well commented, attribute id should be category name.

    Thanks again

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>