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.
26 comments
is this correctly works on magento 1.9.2, it seems works ascending order but descending order seems not working. anyone have updated php code for fix that issue
Hi
If I want to show just last one weeks best selling, how can I implement it using same code. Can somebody help me with this.
Nice info! This was really helpful. Looking for more such blogs.
Hi,
you should better use an inner join instead of a left join. A category with many products the impact is quite huge. 2 seconds vs. 60 seconds without caching and so on.
Greetings Jan
But you will only see sold products of course 😉
I implemented this module, it seems to be doing something, an option to search by popularity does appear and when selected seems to be rearranging the products, however it is doing so incorrectly, I check our bestsellers in reports section and they don’t match what this module returns. I am guessing that the database query is incorrect. I am using Magento 1.9.1.
I figured out why I was not getting the desired result. But I do have another problem -> I would like to have this option as default option selected by default, but this option does not appear in admin panel where I can change the default option. How can I fix this?
Why weren’t you getting the products sorted in the proper order? Right now, even I am facing the same issue
I follow step by step but option for sorting not showing. What could be wrong? I see Inchoo extenssion in admin pannel.
i get this error You cannot define a correlation name ‘sfoi’ more than once
Same here.
public function setCollection($collection)
{
parent::setCollection($collection);
if(Mage::registry(‘getcollect’) == true){return false;}
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();
}
Mage::register(‘getcollect’, true);
}
return $this;
}
Works great. Thanks
Which version of magento do you have?
can you please provide me in zip file so that I can upload to my localhost website. I need to test. I try many time but not working. I am new in magento.
Hi, Thank you for writing this article. I advised my client to do this. I still do have a question about the implementation. (I already have Magento recognizing the module in system –> Config –> Advanced. )
My only problem is; where to put the three files:
“Our app/code/local/Inchoo_Catalog_Block_Product_List_Toolbar should look like this:”
I saved them as a .php-file and copied alle three files to three directories **trial and error 😉 **
/app/code/local/Inchoo/Catalog
/app/code/local/Inchoo/Catalog/etc
/app/code/local/Inchoo/Catalog/Model/Resource/Product
I have saved the config.xml here:
/app/code/local/Inchoo/Catalog/etc
Am I missing something and did I not read your post granurally enough? I hope you could maybe give me a hint here. I am a marketeer so I think that is why I cannot solve it, I guess.
I am hoping to hearing from you.
Best Regards from Peter
Thanks a lot Inchoo team for the solution
This solution leads to performance Issues on large catalogs. There is a reason why attributes have an option to use them for sorting. A good solution would be using a custom attribute initially filled by update script and continuesly updated via some checkout event.
i make the module same to same
there is sold quantity option also available
when i click on it page also refreshed but
grid view is not ordered by sold quantity
(i just copy and pasting the code)..
and how we make it as the default sort?
i found it by adding
public function getProductListDefaultSortBy($store = null) {
return Mage::getStoreConfig(‘qty_ordered’, $store);
}
to class Inchoo_Catalog_Model_Config
Nice point, but does not work for me, any idea why?
Hello
Actually I didn’t understand in this file . . please tell me step by step how to create module for this and how to edit files . . give filename also . .
please its my humble request .
please send me .
i need this . otherwise i will lose my job
Hello Michael, thanks for the comment!
Even though we don’t usually touch the core files or use rewrites if they’re not necessary, this was one of the ways to get this done in just a few simple steps.
There sure are more ways to get around this and I might cover it in another post.
Hi Toni, thanks for your contribution to magento knowledge. You don´t need rewrites to reach your aim! I would prefer a solution without rewriting core classes.