Positioning in anchored categories with subcategories

Question

I have a category structure like below, where I have products onnly in LC1 and LC2. All of the categories are anchored, so you can see all of producsts in LCs in any of the higher categories.

Top Cat
-- Sub Cat
   -- Lowest Cat 1 (LC1)
   -- Lowest Cat 2 (LC2)
etc...

I'm having a problem where product positions assigned in the LCs are not working as they are assigned in Top Cat and Sub Cat. The position values only hold when you are in categories that are not anchoring any subcategories.

This is apparently how product positions work, according to discussions from a while back (here and here). Some proposed solutions include

1. Assigning the products in LCs to their parent categories with same positions values
2. Unchecking "Use Config Settings" of the anchoring parent categories and selecting Best Value (which is the position value). This poster seemed like he had only a 2-tier category structure.

(1) is not an option for us. I tried (2), but it doesn't work. I don't know if it's because we have a deeper category tree. I have seem Marius's post that seems relevant, but I'd like to know if there is an option that will allow my product positions values to be displayed according to the position values, discarding how many or how deep subcategories are anchored?

I use Magento EE 1.12.

Answer 1

Building on what I explained here I think the best solution to achieve what you need is to rewrite the method Mage_Catalog_Model_Resource_Category_Indexer_Product::_refreshAnchorRelations and add your own logic.
I mean, instead of the default algorithm for determining the position:

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

try to use something like this:

$position = 'MIN('.
        $adapter->getCheckSql(
            'cp.category_id = ce.entity_id',
            'cp.position',
            'cp.position'//just the product position here instead of the full expression
        )
    .')';

Not sure if the code above works, I didn't test it. I just wanted to emphasize the idea.
After the modifications, rebuild your indexes.

Answer 2

This is working for community but not working for enterprise for enterprise here is the code

Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh :: _getAnchorCategoriesSelect()

UPDATE FROM

'position'      => new Zend_Db_Expr('ccp.position + 10000'),

TO

'position'      => new Zend_Db_Expr('ccp.position'),

Hope this will help you.

Answer 3

just rewrite Mage_Catalog_Model_Resource_Category_Indexer_Product

with

public function reindexAll()    {
              ...
              ...
              $position = 'MIN('.
                $idxAdapter->getCheckSql(
                    'ca.category_id = ce.entity_id',
                    $idxAdapter->quoteIdentifier('cp.position'),

                    // START NEW CODE
                    // add parent cat-position to calulation
                    '('.$idxAdapter->quoteIdentifier('cep.position').' + 1 * 100000) * ' .
                    // END NEW CODE

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


            $select = $idxAdapter->select()
                ->useStraightJoin(true)
                ...
                ...
                ->joinInner(
                    array('ce' => $this->_categoryTable),
                    $idxAdapter->quoteIdentifier('ce.path') . ' LIKE ' .
                    $idxAdapter->quoteIdentifier('ca.path') . ' OR ce.entity_id = ca.category_id',
                    array()
                )

                // START NEW CODE
                // join category_entity for parent cats 

                ->joinInner(
                    array('cep' => $this->_categoryTable),
                    'ce.parent_id = cep.entity_id',
                    array()
                )

                // END NEW CODE

                ->joinInner(
                    ...
...
}