Sort products by sold quantity in Magento
Magento by default comes with a few basic product sorting options such as sorting by product position, name or price. In this article you’ll learn how to sort products by how many times they have been sold.
To do this we have to override some of Magento’s core files.
Modifying core files is bad practice, so we’ll rather create our own module to accomplish the same functionality and remain upgradeable.
I’ll assume you know how to set-up your module and create its corresponding file (app/etc/modules/Inchoo_Catalog.xml) so Magento recognizes our module.
Now that we have our module ready and recognized by Magento, lets create our config.xml at Inchoo/Catalog/etc/config.xml:
<config>
<modules>
<Inchoo_Catalog>
<version>0.1.0</version>
</Inchoo_Catalog>
</modules>
<global>
<blocks>
<catalog>
<rewrite>
<product_list_toolbar>Inchoo_Catalog_Block_Product_List_Toolbar</product_list_toolbar>
</rewrite>
</catalog>
</blocks>
<models>
<catalog>
<rewrite>
<config>Inchoo_Catalog_Model_Config</config>
</rewrite>
</catalog>
<catalog_resource>
<rewrite>
<product_collection>Inchoo_Catalog_Model_Resource_Product_Collection</product_collection>
</rewrite>
</catalog_resource>
</models>
</global>
</config>
As you might have noticed we are overriding the following three files:
- Mage_Catalog_Block_Product_List_Toolbar
- Mage_Catalog_Model_Config
- Mage_Catalog_Model_Resource_Product_Collection
Our app/code/local/Inchoo_Catalog_Block_Product_List_Toolbar should look like this:
class Inchoo_Catalog_Block_Product_List_Toolbar extends Mage_Catalog_Block_Product_List_Toolbar
{
public function setCollection($collection)
{
parent::setCollection($collection);
if ($this->getCurrentOrder()) {
if($this->getCurrentOrder() == 'qty_ordered') {
$this->getCollection()->getSelect()
->joinLeft(
array('sfoi' => $collection->getResource()->getTable('sales/order_item')),
'e.entity_id = sfoi.product_id',
array('qty_ordered' => 'SUM(sfoi.qty_ordered)')
)
->group('e.entity_id')
->order('qty_ordered ' . $this->getCurrentDirection());
} else {
$this->getCollection()
->setOrder($this->getCurrentOrder(), $this->getCurrentDirection())->getSelect();
}
}
return $this;
}
}
We are extending all the Mage_Catalog_Block_Product_List_Toolbar properties and methods but we are overriding the setCollection() method with our own.
Our Inchoo_Catalog_Model_Config is actually very simple:
class Inchoo_Catalog_Model_Config extends Mage_Catalog_Model_Config
{
public function getAttributeUsedForSortByArray()
{
return array_merge(
parent::getAttributeUsedForSortByArray(),
array('qty_ordered' => Mage::helper('catalog')->__('Sold quantity'))
);
}
}
By now the sorting on the category page should actually work, but we have a little problem with pagination as it doesn’t display the correct amount of records found. We can fix this with the following code in Inchoo/Catalog/Model/Resource/Product/Collection.php:
class Inchoo_Catalog_Model_Resource_Product_Collection extends Mage_Catalog_Model_Resource_Product_Collection
{
protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
{
$this->_renderFilters();
$countSelect = (is_null($select)) ?
$this->_getClearSelect() :
$this->_buildClearSelect($select);
if(count($countSelect->getPart(Zend_Db_Select::GROUP)) > 0) {
$countSelect->reset(Zend_Db_Select::GROUP);
}
$countSelect->columns('COUNT(DISTINCT e.entity_id)');
if ($resetLeftJoins) {
$countSelect->resetJoinLeft();
}
return $countSelect;
}
}
And that’s it. After taking 4 little steps our Magento site now is able to sort products by sold quantity.
Hope you enjoyed! 🙂
Note: This is a revamp of an article originally written in November 2008.