Magento – Category sort order for different stores

I was struggling with widget sort order today, too, and I found this to be strange behaviour. The root cause of this issue is /app/code/core/Mage/Core/Model/Resource/Layout.php in current 1.9 core (which looked the same down in 1.6):

/**
 * Retrieve layout updates by handle
 *
 * @param string $handle
 * @param array $params
 * @return string
 */
public function fetchUpdatesByHandle($handle, $params = array())
{
    $bind = array(
        'store_id'  => Mage::app()->getStore()->getId(),
        'area'      => Mage::getSingleton('core/design_package')->getArea(),
        'package'   => Mage::getSingleton('core/design_package')->getPackageName(),
        'theme'     => Mage::getSingleton('core/design_package')->getTheme('layout')
    );

    foreach ($params as $key => $value) {
        if (isset($bind[$key])) {
            $bind[$key] = $value;
        }
    }
    $bind['layout_update_handle'] = $handle;
    $result = '';

    $readAdapter = $this->_getReadAdapter();
    if ($readAdapter) {
        $select = $readAdapter->select()
            ->from(array('layout_update' => $this->getMainTable()), array('xml'))
            ->join(array('link'=>$this->getTable('core/layout_link')), 
                    'link.layout_update_id=layout_update.layout_update_id',
                    '')
            ->where('link.store_id IN (0, :store_id)')
            ->where('link.area = :area')
            ->where('link.package = :package')
            ->where('link.theme = :theme')
            ->where('layout_update.handle = :layout_update_handle')
            ->order('layout_update.sort_order ' . Varien_Db_Select::SQL_ASC);

        $result = join('', $readAdapter->fetchCol($select, $bind));
    }
    return $result;
}

What happens here is that sort order is only used for sorting layout updates for a unique combination of store, area (e.g. frontend, package (base or customer in your case), theme (default) and layout handle (e.g. catalog_category_view).

Therefore, across different combinations, sort order does not yield the expected results.

I found a solution by rewriting two core classes and put it in an extension here: https://github.com/gruenspar/magento-sorted-db-layouts

It is untested in this exact form (as we have some more rewrites on these classes, unfortunately), but the main idea is working flawlessly for us.

If you encounter issues when installing it, let me know.

I resolve this issue by re-indexing magento indexer:reindex