I have to implement the following scenario.
The default sort order in category (by position) should ignore the position set for the products in the category.
Instead when sorting by position it should follow this rule:

  1. Products added in the last X days at the top (X is configurable).
  2. Products that have a discount (special price or catalog rule) at the bottom.
  3. Rest of the products in between the 2 cases above.

My approach is to rewrite Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSort(). If the attribute code passed to it is position I should execute my custom code. Otherwise parent::addAttributeToSort().

But what should my custom code be?

All products are simple or configurable.

Running a cron that updates the positions from time to time based on my rules is not a valid option in this case.


Ok. I should wait an hour more before posting a question.
This seams to work.
I've rewritten Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSort() and made it look like this (not a pretty code but works, layered navigation and all):

class Namespace_Catalog_Model_Resource_Product_Collection
    extends Mage_Catalog_Model_Resource_Product_Collection {
    public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC){
        //don't screw up the admin sorting.
        if (Mage::app()->getStore()->getId() == Mage_Core_Model_App::ADMIN_STORE_ID){
            return parent::addAttributeToSort($attribute, $dir);
        if ($attribute == 'position') { //if should sort by position
            $newLimit = SOME code to get the limit date for products added after X days;
                "(CASE WHEN {{created_at}} >= '{$newLimit}' THEN 0 ELSE 1 END)",
            ); //select a 0|1 flag field. If the product is new then it has the value 0 else it's 1
                '(CASE WHEN (price_index.price - price_index.min_price) > 0 THEN 1 ELSE 0 END)',
            );// select a 0|1 flag field. If the product has a discount then 1 else 0
            $this->getSelect()->order(array('sort_position '.$dir, 'discount '.$dir)); //sort by my new fields keeping the direction selected by the user
            return $this;
        else { //if should sort by something else let Magento do it's magic.
            return parent::addAttributeToSort($attribute, $dir);
  • Just add the code for a flat table in case you have that enabled
    – stephen
    Sep 1 '16 at 7:42

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.