Magento – Some products are missing in table catalog_product_index_price!

How To Display Best Selling Products On Magento Store Home Page

Showing best selling products on your store’s home page is a great option especially if you are looking to promote best selling products even more. If you are looking to display best selling products on your home page you can do that by following simple ways:

Show best selling products by creating best selling “attribute” and assign that attribute to the desired product. Query your database and find out actual data and then show best selling products based on your query. Create a category called “Bestselling” and assign your desired products to this particular category. In this article i will explain how to show best selling products based on a category called “bestselling”.

Typically best selling product grid is either displayed on your store’s home page or sidebar. In our example we will try to display best selling product grid on home page.

Create “Bestselling” Category

Let’s create a new category “Bestselling” from admin. Go to Admin => Catalog => Categories => Manage Categories

Now, let’s set this category to “Is Active: No” just so that it does not show up in our category list in top navigation and other areas where you might be displaying list of all categories. At the top of the page you will notice “category id” which is our desired category id to fetch bestselling products and display on home page. In my example we will work with “Bestselling (id:179)”

Let’s assign few products under this “Bestselling” category which should be displayed on your store’s home page.

Code to Show Best Selling Products on Home Page

As we have Bestselling category created and some sample products assigned to this particular category we are ready to create a new code which will fetch all the products from category id 179 and display on our home page.

Open your ftp editor and go to app => design => frontend => base => default => template => catalog => product and create new php file called “bestsellers.phtml”. (considering that you are using default theme {change theme name and path according to your current selected theme})

Put the following code in bestsellers.phtml

    <?php
$_productCollection=$this->getLoadedProductCollection();
$_helper = $this->helper('catalog/output');
if ($_productCollection->count()):
$_iterator = 0;

foreach ($_productCollection as $_product): ?>

<div> <a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->stripTags($this->getImageLabel($_product, 'small_image'), null, true) ?>"><img src="<?php echo $this->helper('catalog/image')->init($_product, 'small_image')->resize(145); ?>" width="120" height="120" alt="<?php echo $this->stripTags($this->getImageLabel($_product, 'small_image'), null, true) ?>" /></a>
<p><?php echo $_helper->productAttribute($_product, $_product->getName(), 'name') ?></p>
<p><span><?php echo $this->getPriceHtml($_product, true) ?></span></p>
<p><a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->stripTags($_product->getName(), null, true) ?>"><img src="images/more-info.gif" width="110" height="30" alt="More Info" /></a></p>
</div>

<?php endforeach; // end products loop ?>
<script type="text/javascript">decorateList('products-list', 'none-recursive')</script>
<?php endif; // if product collection ?>

The above code will fetch product name, price and thumbnail from the system and display at a desired location. You should change the XHTML/CSS depending upon your themes requirements.

Finally we will call this piece of code on our store’s home page. Go to Admin => CMS => Pages and edit your store’s selected home page.

<div id="bestselling-grid">Best Selling Products</div>

{{block type="catalog/product_list" category_id="179" template="catalog/product/bestsellers.phtml"}}

In the above code just change the category_id value you have noted while creating new “Bestselling” category.

We are done. You can further customize this product grid by showing “Add to Cart” button, displaying as list or grid etc.

I would love to hear your experiences of showing best selling products on your store’s home page. Please leave me a comment and let me know. Subscribe our RSS to receive latest Magento updates, tips and tricks.

You are directly operating on Zend_Db_Select which is always a bad idea.

The thing here is, that getLastPageNumber is using getSize, which is \Varien_Data_Collection_Db::getSize and uses \Varien_Data_Collection_Db::getSelectCountSql which is overwritten here \Mage_Catalog_Model_Resource_Product_Collection::getSelectCountSql and implemented with \Mage_Catalog_Model_Resource_Product_Collection::_getSelectCountSql.

Digging deeper in this methods shows:

protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
{
    $this->_renderFilters();
    $countSelect = (is_null($select)) ?
        $this->_getClearSelect() :
        $this->_buildClearSelect($select);
    $countSelect->columns('COUNT(DISTINCT e.entity_id)');
    if ($resetLeftJoins) {
        $countSelect->resetJoinLeft();
    }
    return $countSelect;
}

And I assume your problem is:

    if ($resetLeftJoins) { // true by default
        $countSelect->resetJoinLeft();
    }