How does the Sort Order of Products in Subcategories work?

Question

When adding a product to a category, a sorting order value can be specified.

Imaging now we have a Category "P" set to "Anchor" and various categories "C1" and "C2" which are childs of "P".

How is the order of those products defined, which are only in the sub categories, but displayed when opening the parent category?

Answer 1

Take a look at Mage_Catalog_Model_Resource_Category_Indexer_Product::_refreshAnchorRelations specially these lines:

$position = 'MIN('.
            $adapter->getCheckSql(
                'cp.category_id = ce.entity_id',
                'cp.position',
                '(cc.position + 1) * ('.$adapter->quoteIdentifier('cc.level').' + 1) * 10000 + cp.position'
            )
        .')';

cc and ce are the same catalog_category_entity table and cp is catalog_category_product table.

A product can be in more than one child categories so the product position is a minimum between multiple values.
So basically the product position in the parent category is the minimum between the positions relative to each categories from the table catalog_category_product following this formula:

(child category position + 1) * (child category level + 1) * 10000 + ( product position in catalog_category_product + 1)  

[EDIT]
Conclusion: products in a category with a position higher in the tree will be shown before the ones below.

Answer 2

As Marius and Andreas suggested, you have to rewrite the Mage_Catalog_Model_Resource_Category_Indexer_Product class to change the comportement of _refreshAnchorRelations and reindexAll.

For _refreshAnchorRelations

You have by default

'(cc.position + 1) * ('.$adapter->quoteIdentifier('cc.level').' + 1) * 10000 + cp.position'

change it for

'cp.position + 10000'

For reindexAll

You have by default

'('.$idxAdapter->quoteIdentifier('ce.position').' + 1) * '
.'('.$idxAdapter->quoteIdentifier('ce.level').' + 1 * 10000)'
.' + '.$idxAdapter->quoteIdentifier('cp.position')

change it for

'('.$idxAdapter->quoteIdentifier('cp.position').' + 10000)'

So it will only take the minimum product position. I have added + 10000 because some of our customers enter a negative position.

Answer 3

Note there is a huge mistake between Mage_Catalog_Model_Resource_Category_Indexer_Product::_refreshAnchorRelations() and Mage_Catalog_Model_Resource_Category_Indexer_Product::reindexAll() methods, which will obviously lead to some strange behaviors:

In _refreshAnchorRelations():

$position = 'MIN('.
    $adapter->getCheckSql(
        'cp.category_id = ce.entity_id',
        'cp.position',
        '(cc.position + 1) * ('.$adapter->quoteIdentifier('cc.level').' + 1) * 10000 + cp.position'
    )
.')';

Note that here we have brackets that are surrounding the expression, which gives us this result: (cc.position + 1) * (cc.level + 1) * 10000 + cp.position.

_refreshAnchorRelations() is a protected method called by the following three methods in the same class:

• catalogProductSave()
• catalogProductMassAction()
• catalogCategorySave()

These last are called when you are saving a category or a product in the backend.

In reindexAll():

$position = 'MIN('.
    $idxAdapter->getCheckSql(
        'ca.category_id = ce.entity_id',
        $idxAdapter->quoteIdentifier('cp.position'),
        '('.$idxAdapter->quoteIdentifier('ce.position').' + 1) * '
        .'('.$idxAdapter->quoteIdentifier('ce.level').' + 1 * 10000)'
        .' + '.$idxAdapter->quoteIdentifier('cp.position')
    )
.')';

Here, the brackets surrounding the expression are positioned differently, which give us: (ce.position + 1) * (ce.level + 1 * 10000) + cp.position.

With the priority of the operators, the result obtained here will not be the same at all than with _refreshAnchorRelations() method.

As its name may suggest, the reindexAll() method occurs when you launch the reindexing of catalog_category_product index ("Category Products").