Magento: How to reorder categories alphabetically at source

While working on a large multi-store, multi-lingual Magento website with over 5,000 categories recently, we were asked to order the categories alphabetically.

Magento allows you to set the order of categories very easily, but it doesn't allow a different order per store view, and of course with different languages, not all store views were displaying the categories in alphabetical order.

After a bit of digging, I found the core file which controls the sql to retrieve the category structure. If you are using flat categories (and if not, why not!), then the file lives here;

MAGE ROOT/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php

First things first, never modify a core file, so make a copy of the above file and create the folder structure in the local folder;

MAGE ROOT/app/code/local/Mage/Catalog/Model/Resource/Category/Flat.php

Open the file and find this function;

protected function _loadNodes($parentNode = null, $recursionLevel = 0, $storeId = 0) {

Within this function, find the main select query;

$select = $_conn->select()
            ->from(
                array('main_table' => $this->getMainStoreTable($storeId)),
                array('entity_id',
                    new Zend_Db_Expr('main_table.' . $_conn->quoteIdentifier('name')),
                    new Zend_Db_Expr('main_table.' . $_conn->quoteIdentifier('path')),
                    'is_active',
                    'is_anchor'))
            ->joinLeft(
                array('url_rewrite'=>$this->getTable('core/url_rewrite')),
                'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' .
                $_conn->quoteInto(
                'url_rewrite.product_id IS NULL AND url_rewrite.store_id=? AND ',
                $storeId) .
                $_conn->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')),
                array('request_path' => 'url_rewrite.request_path'))
            ->where('main_table.is_active = ?', '1')
            ->where('main_table.include_in_menu = ?', '1')
            ->order('main_table.position');

Notice that last bit? That's the line we need to modify, like this;

$select = $_conn->select()
            ->from(
                array('main_table' => $this->getMainStoreTable($storeId)),
                array('entity_id',
                    new Zend_Db_Expr('main_table.' . $_conn->quoteIdentifier('name')),
                    new Zend_Db_Expr('main_table.' . $_conn->quoteIdentifier('path')),
                    'is_active',
                    'is_anchor'))
            ->joinLeft(
                array('url_rewrite'=>$this->getTable('core/url_rewrite')),
                'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' .
                $_conn->quoteInto(
                'url_rewrite.product_id IS NULL AND url_rewrite.store_id=? AND ',
                $storeId) .
                $_conn->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')),
                array('request_path' => 'url_rewrite.request_path'))
            ->where('main_table.is_active = ?', '1')
            ->where('main_table.include_in_menu = ?', '1')
            //->order('main_table.position');
->order('main_table.name');

This takes care of the order of categories in tree's and menus. If you are also using the category layered navigation, you need to change this other function in the same file;

protected function _loadNodesLayer($parentNode = null, $recursionLevel = 0, $storeId = 0) {

Modify the select statement in exactly the same way.

You could change this to order by any field you like. Note: main_table refers to the catalog_category_flat_store_[STORE ID] table.

Happy coding!