Magento ver. 1.9.1.0 : Sort Products by Quantity and Price descending

Question

I need to sort products by quantity and price i.e. all the products of category should be listed by maximum quantity. Maximum quantity of product should come first then lower quantity products should be listed. I have achieved this by writing this code in /app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php

public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC)
{
    if ($attribute == 'position') {
   /**** Sorting for quantity starts***************/         
        //join the stock status index table for the current website and check if the product is saleable and the qtu.
        $select = $this->getSelect();
        $select->joinLeft(
            array('stock_qty' => $this->getTable('cataloginventory/stock_status')),
            'e.entity_id = stock_qty.product_id AND stock_qty.website_id='.Mage::app()->getWebsite()->getId(),
            array(
                'salable' => 'stock_qty.stock_status',
                'qty' => 'stock_qty.qty'
            )
        );
        //get the reversed sorting
        //if by position ascending, then you need to sort by qty descending and the other way around
        $reverseDir = ($dir == 'ASC') ? 'DESC' : 'ASC';
        //this is optional - it shows the in stock products above the out of stock ones independent if sorting by position ascending or descending
        $this->getSelect()->order('salable DESC');
        //sort by qty
        $this->getSelect()->order('qty '.$reverseDir);
        return $this;      

/**** Sorting for quantity ends ***************/  

    } elseif($attribute == 'is_saleable'){
        $this->getSelect()->order("is_saleable " . $dir);
        return $this;
    }

    $storeId = $this->getStoreId();
    if ($attribute == 'price' && $storeId != 0) {
        $this->addPriceData();
        $this->getSelect()->order("price_index.min_price {$dir}");

        return $this;
    }

    if ($this->isEnabledFlat()) {
        $column = $this->getEntity()->getAttributeSortColumn($attribute);

        if ($column) {
            $this->getSelect()->order("e.{$column} {$dir}");
        }
        else if (isset($this->_joinFields[$attribute])) {
            $this->getSelect()->order($this->_getAttributeFieldName($attribute) . ' ' . $dir);
        }

        return $this;
    } else {
        $attrInstance = $this->getEntity()->getAttribute($attribute);
        if ($attrInstance && $attrInstance->usesSource()) {
            $attrInstance->getSource()
                ->addValueSortToCollection($this, $dir);
            return $this;
        }
    }

    return parent::addAttributeToSort($attribute, $dir);
}

Now I need to sort products by price descending also i.e. quantity descending with price descending

Product  Quantity Price
-------  -------- -------
  A         100   9000
  B         100   8000
  C         89    7000
  D         80    6000

I also tried by going to System > Configuration > Catalog > Product Listing Sort by = Price . But this does not help me. So I am looking for the solution, and I think the solution is by manipulating the code here

$select = $this->getSelect();
        $select->joinLeft(
            array('stock_qty' => $this->getTable('cataloginventory/stock_status')),
            'e.entity_id = stock_qty.product_id AND stock_qty.website_id='.Mage::app()->getWebsite()->getId(),
            array(
                'salable' => 'stock_qty.stock_status',
                'qty' => 'stock_qty.qty'
            )
        );

Please help me out. Thanks in advance

Amuk Saxena Amuk Saxena · Accepted Answer · 2015-04-16T12:30:58

Finally I found the solution: Add this line of code after sorting quantity by descending order ==> $this->getSelect()->order('price DESC');

$select = $this->getSelect();
        $select->joinLeft(
            array('stock_qty' => $this->getTable('cataloginventory/stock_status')),
            'e.entity_id = stock_qty.product_id AND stock_qty.website_id='.Mage::app()->getWebsite()->getId(),
            array(
                'salable' => 'stock_qty.stock_status',
                'qty' => 'stock_qty.qty'
            )
        );
        //get the reversed sorting
        //if by position ascending, then you need to sort by qty descending and the other way around
        $reverseDir = ($dir == 'ASC') ? 'DESC' : 'ASC';
        //this is optional - it shows the in stock products above the out of stock ones independent if sorting by position ascending or descending
        $this->getSelect()->order('salable DESC');

        //sort by qty
        $this->getSelect()->order('qty '.$reverseDir);
        $this->getSelect()->order('price DESC');
        return $this;

Magento ver. 1.9.1.0 : Sort Products by Quantity and Price descending

1 Answers