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?


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

$position = 'MIN('.
                'cp.category_id = ce.entity_id',
                '(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)  

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

  • I can't compute by hand the exact position values indexed in catalog_category_product_index (I'm getting numbers much larger), but I get the idea.. @Marius, have you ever tried manually modifying these indexed position values? It doesn't seem to affect anything else when I mess around with them on a dev. – musicliftsme Sep 25 '13 at 17:31
  • @Youn Sorry I've never tried to modify them – Marius Sep 25 '13 at 18:44
  • do you understand the logic behind this formula? It's completely the opposite of how I would have weighed things.. unless, of course, you like to assign large position numbers. – musicliftsme Sep 25 '13 at 20:30
  • You also have to adjust Mage_Catalog_Model_Resource_Category_Indexer_Product::reindexAll. – Andreas von Studnitz May 27 '14 at 11:56

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.

  • hm I do not want to change anything - the question was just about knowing how that stuff works :) thanks anyways – Alex Oct 13 '16 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.