Skip to main content
Magento Store Image

How to dynamically display your Magento store's bestselling products

Category
Development,
eCommerce,
Technical

One of our clients is an online motorcycle clothing and accessories business called Mototo.com. Mototo.com is powered by Magento, the popular open source ecommerce solution. We've found Magento to be great to work with. It provides a huge amount of functionality 'out of the box' and allows you to build an enterprise level ecommerce solution relatively quickly and therefore cheaply.

A piece of bespoke functionality we created for Mototo.com was a dynamic 'Bestsellers' page. The code behind this page queries the store's underlying database and pulls back a list of products ordered by the number sold descending. To achieve this functionality we followed online consensus rather than re-inventing the wheel. Initially results looked good but recently we noticed that this page wasn't producing results that matched the audited sales report, although they were close enough to give the appearance that all was OK. As lots of other businesses using Magento are likely to have used the same method we did then our investigation and corrected 'Bestsellers' code should be of interest to the community as a whole so we thought we'd share it here in our blog.

The original 'Bestsellers' code

If you search around the web for how to create a Magento bestsellers page then the core PHP snippet suggested to generate the bestsellers data is a variation on the following:

$collection = Mage::getResourceModel('reports/product_collection');

$collection = $this->_addProductAttributesAndPrices($collection)
    ->addOrderedQty()
    ->setOrder('ordered_qty', 'desc');

What this code is doing is re-using the functionality within the class Mage_Reports_Model_Resource_Product_Collection to pull back the bestselling products list. This makes reasonable sense as this same class is used to provide the data for the Magento Admin Page's 'Reports->Products->Products Ordered' report.

The key function in the above snippet is addOrderedQty() which calls the corresponding method in the class Mage_Reports_Model_Resource_Product_Collection. The resulting SQL query returned by this function is as follows:

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 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 
WHERE (parent_item_id IS NULL) 
GROUP BY order_items.product_id 
HAVING (SUM(order_items.qty_ordered) > 0);

If you run this query directly on your development database you will see it retrieves all product types (i.e. bundle, configurable, simple etc). This is expected as the query is retrieving data from the sales_flat_order_item table and joining entity data to it for simple products only. The problem we are experiencing is that the data retrieved still contains some simple products that are in fact child products of complex products. For the Mototo.com store this is caused by the raw data within the sales_flat_order_item table. We have no idea what caused this inconsistency, perhaps a third party module or a bug in the native Magento codebase, but given the volumes of data in the table it's easier to work around the data than manually correct it.

The new 'Bestsellers' code

With a bit of research we successfully created a SQL query than retrieves a bestsellers list which matches audited data. This is as follows:

SELECT 
SUM(qty_ordered) AS ordered_qty,
name AS order_items_name,
IF(parent_id IS NOT NULL AND visibility != 4, parent_id, product_id) AS final_product_id
FROM
(SELECT
order_items.qty_ordered,
order_items.name, 
order_items.product_id,
cpr.parent_id,
cat_index.store_id,
cat_index.visibility,
cat_index.category_id
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_relation AS cpr ON cpr.child_id = order_items.product_id
LEFT JOIN catalog_category_product_index AS cat_index ON cat_index.product_id = order_items.product_id
WHERE parent_item_id IS NULL
AND cat_index.store_id = 1
AND category_id = 2) AS T1
GROUP BY final_product_id
ORDER by ordered_qty DESC;

We can extend the Mage_Reports_Model_Resource_Product_Collection class overwriting the original addOrderQty method with our own method that uses the above query. We've tried to keep the syntax of the overwritten method similar to the original:

class ZodiacMedia_Bestsellers_Model_Resource_Product_Collection extends Mage_Reports_Model_Resource_Product_Collection {
    
    /**
     * Add number of orders for products
     *
     * @param string $from
     * @param string $to
     * @return Mage_Reports_Model_Resource_Product_Collection
     */
    public function addOrderedQty($from = '', $to = '') {
        $adapter              = $this->getConnection();
        $orderTableAliasName  = $adapter->quoteIdentifier('order');
    
        $orderJoinCondition   = array(
            $orderTableAliasName . '.entity_id = order_items.order_id',
            $adapter->quoteInto("{$orderTableAliasName}.state <> ?", Mage_Sales_Model_Order::STATE_CANCELED),
        );
            
        $productJoinCondition = array(
            'e.entity_id = T1.final_product_id',
            $adapter->quoteInto('e.entity_type_id = ?', $this->getProductEntityTypeId())
        );
        
        if ($from != '' && $to != '') {
            $fieldName            = $orderTableAliasName . '.created_at';
            $orderJoinCondition[] = $this->_prepareBetweenSql($fieldName, $from, $to);
        }
        
        $subSelect = $this->getSelect()->reset()
            ->from(
                array('order_items' => $this->getTable('sales/order_item')),
                array(
                    'qty_ordered',
                    'name',
                    'final_product_id' => new Zend_Db_Expr("IF(parent_id IS NOT NULL AND visibility != " . Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH . ", parent_id, order_items.product_id)"),
                ))
            ->joinInner(
                array('order' => $this->getTable('sales/order')),
                implode(' AND ', $orderJoinCondition),
                array())
            ->joinLeft(
                array('cpr' => $this->getTable('catalog/product_relation')),
                'cpr.child_id = order_items.product_id',
                array(
                    'parent_id',
                ))
            ->joinLeft(
                array('cat_index' => $this->getTable('catalog/category_product_index')),
                'cat_index.product_id = order_items.product_id',
                array(
                    'store_id',
                    'visibility',
                    'category_id',
                ))
            ->where('parent_item_id IS NULL')
            ->where('cat_index.store_id = ?', Mage::app()->getStore()->getId())
            ->where('category_id = ?', Mage::app()->getStore()->getRootCategoryId());
            
        $subSelectString = '(' . $subSelect->__toString() . ')';
        
        $this->getSelect()->reset()
            ->from(
                array('T1' => new Zend_Db_Expr($subSelectString)),
                array(
                    'ordered_qty' => 'SUM(qty_ordered)',
                    'order_items_name' => 'name',
                    'entity_id' => 'final_product_id',
                ))
            ->joinLeft(
                array('e' => $this->getProductEntityTableName()),
                implode(' AND ', $productJoinCondition),
                array(
                    'entity_type_id' => 'e.entity_type_id',
                    'attribute_set_id' => 'e.attribute_set_id',
                    'type_id' => 'e.type_id',
                    'sku' => 'e.sku',
                    'has_options' => 'e.has_options',
                    'required_options' => 'e.required_options',
                    'created_at' => 'e.created_at',
                    'updated_at' => 'e.updated_at'
                ))
            ->group('final_product_id');
        
        return $this;
    }
}

To integrate this method into a bestsellers widget it is then a case of using a slightly altered code snippet to the original one presented at the start of this post:

$collection = Mage::getResourceModel('bestsellers/product_collection');

$collection = $this->_addProductAttributesAndPrices($collection)
    ->addOrderedQty()
    ->setOrder('ordered_qty', 'desc')
    ->setPageSize(12)
    ->setCurPage(1);

The difference here is that we're using our new extended class rather than Magento's original one.

How to use the new 'Bestsellers' code

We've packaged the new bestsellers code up into a Bestellers Magento Widget that can be downloaded via Magento Connect. You can easily create a bestellers page in your Magento store using the standard Magento Admin Page user interface as follows:

  1. Install the Zodiac Media Bestsellers extension from Magento Connect.
  2. Create a new page within the 'CMS->Pages' section of Magento's admin panel with a page title of 'Bestsellers' and a URL of bestsellers.
  3. For the content of the page click on 'Insert Widget', select 'Zodiac Media Bestsellers' and fill in the rest of the Widget form settings. Also set the 'Content Heading' within the 'Content' section to be 'Bestsellers'.
  4. Select your preferred page layout within the 'Design' section of the new CMS page.

The result should look something like this: http://www.mototo.com/bestsellers.

I hope this blog post was useful to you. If you're struggling with Magento development and are looking for experienced Magento developers then do please get in touch with us to see if we can help you.