How to get best-selling category base on most selling product and one more? It should not take too much loading time.

3 Answers 3


We have developped a module that includes several custom reports including a bestselling category report.

The idea is to first retrieve the bestseller products like this:

    $bestSellers = Mage::getResourceModel('reports/product_collection')
        ->addOrderedQtyAndTotal($sDate, $eDate)

The addOrderedQtyAndTotal method is declared here: https://github.com/digitalpianism/customreports/blob/master/app/code/community/DigitalPianism/CustomReports/Model/Reports/Resource/Product/Collection.php

Then using the iterator walk to handle massive collection instead of using foreach to improve performance:

    Mage::getSingleton('core/resource_iterator')->walk($bestSellers->getSelect(), array(array($this, 'fillBestsellersArray')));

In the callback method, we retrieve all categories of each product:

    $categories = Mage::getResourceModel('catalog/category_collection')
                        'category_id = entity_id',
                    ->addFieldToFilter('product_id', $productId);

You can find details here if you need: https://github.com/digitalpianism/customreports/blob/master/app/code/community/DigitalPianism/CustomReports/Block/Bestsellersbycategory/Grid.php

An important point you've got to keep in mind for such reports is that these data may not be totally accurate for the following reasons:

  • A product can be moved to a different category after an order has been placed
  • A category can be deleted
  • A product can be in more than one category
  • For those reasons, if a product is in several categories, it will be counted as a sale for all of these categories as we don't know which referrer page the customers used to purchase that product (if it's from a category, from the search or via direct link).

The module is free and available on Magento Connect, GitHub and our website, feel free to check it out and modify it to fit your needs.


Use below code:

//bestseller product collection
$visibility = array(

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

//best seller category collection according to product collection
foreach ($_productCollection as $product) {
    $categories = array_unique(array_merge($categories, $product->getCategoryIds()));

foreach ($categories as $child) {
    $cat = Mage::getModel('catalog/category')
    echo $catName = $cat->getName();

I hope this is helpful to you.

  • Thanks, but it will take too much time to load because I have 40k+ product, 2k category and 28k+ order.
    – Nits
    Commented Apr 14, 2016 at 7:18
  • Please explain what your code is doing and how it will solve the OP's problem. "Code only" answers often aren't very helpful for future readers to understand the actual solution.
    – 7ochem
    Commented Apr 14, 2016 at 8:03

You can pull the current BestSellers from the sales reports built into magento.

The code below was pulled from my (not free) Dynamic Category Products extension, which can build best sellers categories (more on this further down, as a non-coding solution) - there are some dependent methods, which I include for completeness

First, a generic method that pulls in the report collection:

     * Get the sales report collection
     * @return object
    protected function getCollection()
        $storeId = Mage::app()->getStore()->getId();

        return Mage::getResourceModel('reports/product_collection')

The two routines to getthe dates, to allow setting a date range for the best sellers

protected function getDateBack($value)
        return date(
            'Y-m-j G:i:s',
            strtotime('-' . $value . ' day' . $this->getTodayDate())

    protected function getTodayDate()
        return Mage::app()->getLocale()->date()->toString(

and then finally, the sugar ;) - the code has been modified after pulling from the module, and has been tested (had to strip out a bunch of code that involves merging collections)

Placed a die to output the sql, so you obviously don't want that.

     * Calculate bestsellers x days back
     * @return boolean
    public function bestSellers()
        try {
            $daysBack = 100;
            $customCollection = $this->getCollection();
                $this->getDateBack($daysBack = 100), $this->getTodayDate()
                'ordered_qty ' . Zend_Db_Select::SQL_DESC
                'order_items_name ' . Zend_Db_Select::SQL_ASC
            $customCollection->addWebsiteFilter(1); // get current website id in your code here!

            die((string) $customCollection->getSelect());

            return true;

        } catch (Exception $e) {

        return false;

The above, would generate SQL like such:

    SUM(order_items.qty_ordered) AS `ordered_qty`,
    `order_items`.`name` AS `order_items_name`,
    `order_items`.`product_id` AS `entity_id`,
    `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'
        AND (`order`.created_at BETWEEN '2016-01-5 0:00:00' AND '2016-04-14')
        LEFT JOIN
    `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped' , 'configurable',
        AND e.entity_id = order_items.product_id
        AND e.entity_type_id = 4
        INNER JOIN
    `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id
        AND product_website.website_id IN ('1')
    (parent_item_id IS NULL)
GROUP BY `order_items`.`product_id`
HAVING (SUM(order_items.qty_ordered) > 0)
ORDER BY `ordered_qty` DESC , `order_items_name` ASC

A non coding solution: Use my Dynamic Category Products extension, which is known to work with 300k+ catalogs! The module will give you the ability to populate categories dynamically with products. From there you can deal with them as per any category, as the extension does not change the product<->category association system of magento core. Products are assigned 100% properly to the categories

The extension will also give ability for:

Best Sellers by revenue Best Sellers by profit

And Least Sellers for all the above :)

A view of the category based rules.

enter image description here

Good luck!

  • 1
    Sorry but I cannot afford this paid extension.
    – Nits
    Commented Apr 14, 2016 at 10:19
  • Module offered as alternative solution. The code supplied as part of answer is all you need to get best sellers collection
    – ProxiBlue
    Commented Apr 14, 2016 at 10:57
  • @Nits I do also now realise that i slightly misunderstood question. You are asking fir best selling category. Not how to create a best selling category.
    – ProxiBlue
    Commented Apr 14, 2016 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.