Bestseller products in Magento

By: Damir Korpar, Last updated: Sep 26, 2019 | Published: May 07, 2014
Bestsellers

Bestseller products is one of the features people tend to ask about and look for when it comes to Magento. Default installation already has bestseller products option included…but these are static ones defined in a CMS page. We’re going to take it to the next level and automate it.

Magento already uses bestseller products aggregation that can be checked under Admin > Reports > Products > Bestsellers. Thanks to this aggregated data we no longer need to determine the total of all the orders to see which products are sold the most. By the variety of aggregation data we’re able to get the most popular products not only from the beginning, but for each specific day, month or year.

There’s many ways of doing this. What I’ve found the most useful to me is to join product collection with the monthly bestseller aggregation. This way is fast and products can always be accessed even though the aggregation table is empty.

class Inchoo_Damir_Block_Bestsellers extends Mage_Core_Block_Template
{
    public function getBestsellerProducts()
    {
        $storeId = (int) Mage::app()->getStore()->getId();
 
        // Date
        $date = new Zend_Date();
        $toDate = $date->setDay(1)->getDate()->get('Y-MM-dd');
        $fromDate = $date->subMonth(1)->getDate()->get('Y-MM-dd');
 
        $collection = Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
            ->addStoreFilter()
            ->addPriceData()
            ->addTaxPercents()
            ->addUrlRewrite()
            ->setPageSize(6);
 
        $collection->getSelect()
            ->joinLeft(
                array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')),
                "e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'",
                array('SUM(aggregation.qty_ordered) AS sold_quantity')
            )
            ->group('e.entity_id')
            ->order(array('sold_quantity DESC', 'e.created_at'));
 
        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
 
        return $collection;
    }
}

What the code does is that it returns a product collection joined with the aggregation table with the products sold the most in the last month. If this logic will be or is being used more than once, it’s recommended to place the join logic into catalog product collection file (by extending the file or by using the events) and to call it as a method when constructing the collection.

Now, when the product collection is ready, there are more ways to display them on the frontend. For the purposes of the article let’s replace the original html in the CMS page with a block call. As it can be seen from the code above, I’ve used my own block for the bestsellers logic. Open CMS > Pages, select the row with the identifier “home” and replace the content with the html below:

<div class="col-left side-col">
    <p class="home-callout">&nbsp;</p>
    <p class="home-callout"><img src="{{skin url='images/ph_callout_left_rebel.jpg'}}" alt="" border="0" /></p>
    {{block type="tag/popular" template=“tag/popular.phtml"}}
</div>
<div class="home-spot">
    <p class="home-callout"><img src="{{skin url='images/home_main_callout.jpg'}}" alt="" width="470" border="0" /></p>
    <p class="home-callout"><img src="{{skin url='images/free_shipping_callout.jpg'}}" alt="" width="470" border="0" /></p>
    {{block type="damir/bestsellers" template=“damir/bestsellers.phtml"}}
</div>

This html does nothing more than replace Magento’s div tag that contained static bestseller products with our block call. The next and the last thing that has to be done is to create a template file. The template file uses the same html structure as the CMS page:

<div class="box best-selling">
<h3>Best Selling Products</h3>
<table border="0" cellspacing="0">
    <tbody>
    <?php $counter=0; foreach ($this->getBestsellerProducts() as $product): ?>
        <?php if ($counter%2 == 0): ?><tr class="<?php echo $counter%4 ? 'even' : 'odd'; ?>"><?php endif ?>
        <td>
            <a href="<?php echo $product->getProductUrl() ?>"><img class="product-img" src="<?php echo $this->helper('catalog/image')->init($product, 'small_image')->resize(99); ?>" alt="<?php echo $this->stripTags($this->getImageLabel($product, 'small_image'), null, true) ?>" width="95" border="0" /></a>
            <div class="product-description">
                <p><a href="<?php echo $product->getProductUrl() ?>"><?php echo $this->stripTags($product->getName(), null, true); ?></a></p>
            </div>
        </td>
        <?php if ($counter++%2): ?></tr><?php endif ?>
    <?php endforeach; ?>
    </tbody>
</table>
</div>

This example uses monthly aggregation which I’ve found the most useful. There’s also daily aggregation that can be use and yearly, which is unlikely to be used often. Many Magento extensions can be found around that does the same thing, just a little bit nicely wrapped, but the logic is actually in one fairly simple method.

As it’s highly not recommended to modify the core files, each project basically has to have at least one module to place our own block. If you’re unfamiliar with how to create modules, there’s a nice article to read about the basics of creating a Magento module that I recommend.

Note: This is a revamp of the article originally written in December 2008

Published in:
Leave a comment

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

90 comments

  4. Little confused as to why you are using SUM function on qty_ordered, seems to be doubling the totals.

    array('SUM(aggregation.qty_ordered) AS sold_quantity')

    Also, the query where clause has aggregation.store_id={$storeId} even though above ->addStoreFilter() is stated above?

    Likewise, is the products meant to be different from Reports -> products -> bestsellersc?

    Reply

  5. Thanks for sharing.
    For now the code is not adapted to work with flat catalog, and I fixed this issuse.

    class Mage_Catalog_Block_Product_Bestseller extends Mage_Catalog_Block_Product_Abstract {

    protected function _beforeToHtml(){
    // Get limit products
    $limitProducts = $this->getLimit();
    if($limitProducts == “”) $limitProducts = 12;

    $storeId = Mage::app()->getStore()->getId();
    $products = Mage::getResourceModel(‘reports/product_collection’)
    ->addAttributeToSelect(‘*’)
    ->addAttributeToSelect(array(‘name’, ‘price’, ‘small_image’))
    ->addOrderedQty()
    ->setStoreId($storeId)
    ->addStoreFilter($storeId)
    ->setOrder(‘ordered_qty’, ‘desc’); // Best sellers on top

    // Enabled flat catalog product
    if (Mage::helper(‘catalog/product_flat’)->isEnabled()) {
    $products->getSelect()->joinInner(array(‘e2’ => ‘catalog_product_flat_’.$storeId), ‘e2.entity_id = e.entity_id’);
    }

    Mage::getSingleton(‘catalog/product_status’)->addVisibleFilterToCollection($products);
    Mage::getSingleton(‘catalog/product_visibility’)->addVisibleInCatalogFilterToCollection($products);

    // Get best sellers for specific category
    if($categoryId = $this->getData(‘category_id’)){
    $category = Mage::getModel(‘catalog/category’)->load($categoryId);
    $products->addCategoryFilter($category);
    }

    // Set limit products
    $products->setPageSize($limitProducts)->setCurPage(1);

    $this->setProductCollection($products);

    return parent::_beforeToHtml();
    }
    }

    Reply

  6. That is one amazing work. I were performing a search and displaying results ordered by bestsellers in dropdown. Your code saved my day. Thank you very much.

    Reply

  7. If you see strange results from this bestsellers collection remember that it is sorting on visible products, if your bestseller is a child of a grouped or configurable product which is not visible in the database then you will not see the product. To work around this you need to manually filter all results and detect child products and then load the parent into a new collection. Then you will see accurate bestseller data.

    Reply

  10. It would be great if this could be extended to join simple products with the configurables where sold¬quantity return NULL so that the configurable products (visible frontend) can show in the bestsellers block for their simple products (not visible in frontend) sales.

    This modification currently doesn’t support that so can only really be used in a catalog where all products are visible in the frontend or those that don’t have product options.

    Reply

    1. Add category filter to product collection

      $category = Mage::getModel('catalog/category')->load(35); // 35 is category id
$collection = Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
            ->addCategoryFilter($category)
            ->addStoreFilter()
            ->addPriceData()
            ->addTaxPercents()
            ->addUrlRewrite()
            ->setPageSize(6);

  12. I want to display products not bestseller in admin page of report sales, How to do?
    Can you help me. thanks all

    Reply

  16. hello
    thank you for useful post, magneto gives us great base potential for ecommerce. But there are some cases when base magento functionality is not enough and i think that for bestsellers would be suitable use an extension with big potential. i’m speaking about http://amasty.com/improved-sorting.html

    i think that it would be interesting to read some information.

    thank you

    Reply

  19. Works well, but I found that enabling flat products breaks this code, untill you add an innerjoin and move the visibility filter down…

    e.g

            /* @var $products Mage_Reports_Model_Resource_Product_Collection */
        $products = Mage::getResourceModel('reports/product_collection')
            //->addAttributeToSelect('*')
            ->addAttributeToSelect(array('name','price','small_image'))
            ->addOrderedQty()
            ->setStoreId($storeId)
            ->addStoreFilter($storeId)
            ->setOrder('ordered_qty','desc'); //best sellers on top

            if ($this->isEnabledFlat()) {
                $products->getSelect()->joinInner(array('e2' => 'catalog_product_flat_'.$storeId), 'e2.entity_id = e.entity_id');
            }

            $products->addAttributeToFilter('visibility', $visibility);
    Reply

  20. Hi, I tried your code, but nothing was showed on my home screen. No data was displayed. I work with magento 1.7 and I have a lot of problems with it. Maybe you know something what went wrong. I used your latest bestseller.zip
    Greetings
    Daniel

    Reply

  24. Hi
    I have magento 1.6.2.0
    where exatle a have to put the inchoo dir???
    i put in
    \app\design\frontend\base\default\template\inchoo…

    in this case, the block is same????
    {{block type=”core/template” template=”inchoo/bestseller.phtml”}}

    Reply

  25. When I Go to your Demo Admin interface
    – System > Configuration > Catalog
    – set Use Flat Catalog Category Yes
    – set Use Flat Catalog Product Yes
    – Reindex the tables
    – Go to frontend end see the bugs…thats it!

    SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`enable_googlecheckout`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_imported`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `e`.`name`, `e`.`price`, `e`.`small_image`, `cat_index`.`position` AS `cat_index_position` FROM `sales_flat_order_item` AS `order_items`
    INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> ‘canceled’
    LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN (‘grouped’, ‘configurable’, ‘bundle’)) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4
    INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0
    INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=’1′ AND cat_index.visibility IN(2, 4) AND cat_index.category_id=’2′ WHERE (parent_item_id IS NULL) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` desc LIMIT 8

    Trace:
    #0 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
    #1 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Zend\Db\Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
    #2 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Zend\Db\Adapter\Abstract.php(479): Zend_Db_Statement->execute(Array)
    #3 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query(‘SELECT SUM(orde…’, Array)
    #4 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Varien\Db\Adapter\Pdo\Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query(‘SELECT SUM(orde…’, Array)
    #5 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Zend\Db\Adapter\Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(‘SELECT SUM(orde…’, Array)
    #6 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Varien\Data\Collection\Db.php(687): Zend_Db_Adapter_Abstract->fetchAll(‘SELECT SUM(orde…’, Array)
    #7 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Eav\Model\Entity\Collection\Abstract.php(1007): Varien_Data_Collection_Db->_fetchAll(‘SELECT SUM(orde…’)
    #8 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Eav\Model\Entity\Collection\Abstract.php(831): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
    #9 D:\xampp\htdocs\magentoma\ma_furniturestore\lib\Varien\Data\Collection.php(740): Mage_Eav_Model_Entity_Collection_Abstract->load()
    #10 D:\xampp\htdocs\magentoma\ma_furniturestore\app\design\frontend\default\ma_furniturestore_orange\template\magentothem\bestsellerproductvertscroller\bestsellerleft.phtml(42): Varien_Data_Collection->count()
    #11 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Template.php(241): include(‘D:\xampp\htdocs…’)
    #12 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView(‘frontend\defaul…’)
    #13 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
    #14 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Template->_toHtml()
    #15 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Text\List.php(43): Mage_Core_Block_Abstract->toHtml()
    #16 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
    #17 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
    #18 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml(‘left’, true)
    #19 D:\xampp\htdocs\magentoma\ma_furniturestore\app\design\frontend\default\ma_furniturestore_orange\template\page\3columns.phtml(52): Mage_Core_Block_Abstract->getChildHtml(‘left’)
    #20 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Template.php(241): include(‘D:\xampp\htdocs…’)
    #21 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView(‘frontend\defaul…’)
    #22 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
    #23 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Template->_toHtml()
    #24 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Model\Layout.php(529): Mage_Core_Block_Abstract->toHtml()
    #25 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Controller\Varien\Action.php(391): Mage_Core_Model_Layout->getOutput()
    #26 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Cms\Helper\Page.php(132): Mage_Core_Controller_Varien_Action->renderLayout()
    #27 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Cms\Helper\Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_IndexController), ‘home’)
    #28 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Cms\controllers\IndexController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_IndexController), ‘home’)
    #29 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Controller\Varien\Action.php(420): Mage_Cms_IndexController->indexAction()
    #30 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch(‘index’)
    #31 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Controller\Varien\Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
    #32 D:\xampp\htdocs\magentoma\ma_furniturestore\app\code\core\Mage\Core\Model\App.php(347): Mage_Core_Controller_Varien_Front->dispatch()
    #33 D:\xampp\htdocs\magentoma\ma_furniturestore\app\Mage.php(640): Mage_Core_Model_App->run(Array)
    #34 D:\xampp\htdocs\magentoma\ma_furniturestore\index.php(80): Mage::run(”, ‘store’)
    #35 {main}

    Reply

  26. Great post though I can’t get it to work on grouped products, I can display the invisible items of course but it won’t link to grouped item or the item as it’s invisible (being part of the grouped!)!

    Can you help?

    Reply

  28. hey this is very usefull block, let me know how to add “Add TO Cart ” button in this code

    Reply

  30. @Alexander
    Can you please provide a hint about how you use categopry based solution.
    In my case i have the same root category for both store

    THanks

    Reply

  32. I have use magneto 1.6.2 it not working.
    i have put the bestseller.phtml \www\e4hats\magento\app\design\frontend\default\e4hats\template\catalog\product

    i have create block cms->static block

    {{block type=”catalog/product_list”template=”catalog/product/bestseller.phtml”}}

    Reply

  33. On Magento 1.6.2 It is also not working.

    I’ve inserted the file bestseller.phtml to mytemlate/inchoo/

    is there anything else to do?

    PLEASE HELP

    Reply

  34. On upgrade from 1.4 to 1.6 , it is not working.
    It do not shows the addAttributeToSelect attributes in phtml as well as it do not even filter store wise.

    Here is the query it is shoing

    SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at` FROM `tp_sales_flat_order_item` AS `order_items` INNER JOIN `tp_sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> 'cancelled' LEFT JOIN `tp_catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4 WHERE (parent_item_id IS NULL) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` desc LIMIT 4
    Reply

  36. On 1.6.1 i get nothing … no errors and no bestseller … also after cache clearing.

    {{block type=”core/template” template=”myTemplate/bestseller.phtml”}}

    Reply

  37. It drops an exception error in 1.5.1.0 on the line:

    $_productCollection = Mage::getResourceModel('reports/product_collection')
                              ->addAttributeToSelect('*')
                              ->addOrderedQty()
                              ->addAttributeToFilter('visibility', $visibility)
                              ->setOrder('ordered_qty', 'desc');

    any ideas?

    Reply

  39. get price html using following code on bestesller.phtml:

    $storeId = Mage::app()->getStore()->getId(); $_product= Mage::getModel(‘catalog/product’)->setStoreId($storeId)->load($product->entity_id);
    $theProductBlock = new Mage_Catalog_Block_Product; echo $theProductBlock->getPriceHtml($_product, true);

    Reply

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.