Sort products by sold quantity in Magento

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.

You made it all the way down here so you must have enjoyed this post! You may also like:

Shell script for converting configurable to grouped products Tsvetan Stoychev
Tsvetan Stoychev, | 5

Shell script for converting configurable to grouped products

Large number of input variables in Magento Kresimir Banovic
, | 5

Large number of input variables in Magento

Programmatically create bundle products in Magento Petar Sambolek
Petar Sambolek, | 5

Programmatically create bundle products in Magento

26 comments

  1. 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

  2. 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.

  3. 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

  4. 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.

    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?

    2. Why weren’t you getting the products sorted in the proper order? Right now, even I am facing the same issue

  5. I follow step by step but option for sorting not showing. What could be wrong? I see Inchoo extenssion in admin pannel.

    1. 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;
      }

  6. Its return error
    SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'parent_item_id' in where clause is ambiguous
    
  7. 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.

  8. 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

  9. 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.

  10. 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)..

    1. i found it by adding

      public function getProductListDefaultSortBy($store = null) {
      return Mage::getStoreConfig(‘qty_ordered’, $store);
      }

      to class Inchoo_Catalog_Model_Config

  11. 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

  12. 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.

  13. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.