Magento 1.9 – Order product collection by biggest percentage discount

The other day I had to create a new filter option for products which could order them by the largest discount. I made this simple workaround where I joined product collection with table “catalog_product_entity_decimal”, which is the table containing all the prices. “attribute_id” with value 76 is special_price.
Then, I created a new column “discount” which is calculated as:

discount = (price – special_price) / (price / 100)

 

// Join the special price into the discount column.
$store = Mage::app()->getStore();
    $_productCollection->getSelect()
                       ->joinLeft(
                           array( 'sfoi' => 'catalog_product_entity_decimal' ),
                           'e.entity_id = sfoi.entity_id AND sfoi.attribute_id = 76 AND sfoi.store_id = ' . $store->getId(),
                           array( 'discount' => '((price - sfoi.value) / (price / 100))' )
                       );
// Set order to DESC
    $_productCollection->getSelect()->Order( array( new Zend_Db_Expr( 'discount DESC' ) ) );

 


Lukas
Lukas Stranovsky
I am the guy who made this. Sorry for typos.