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"> </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
