What I'm basically trying to do is sort a product collection by sales so that the most sold product is on top.

Since there doesn't seem to be a standard way to do this in Magento, I first want to get the product ID's of the most sold products by category, like this;

$category = Mage::getModel('catalog/category')->load({category_id});
$reports = Mage::getResourceModel('reports/product_collection')
                 ->setOrder('ordered_qty', 'desc');

After which I will do some magic with these ID's and create my products JSON.
However, first I'd have to filter the reports per category, but the stock addCategoryFilter() function doesn't work. If I try to do a ->getSelect() after applying the category filter, and search for something like 'category', I find nothing. The function addCategoryFilter() doesn't seem to do anything.

How can I fix this?

  • I know that would work with catalog/product_collection, but not so sure about reports, have you checked the table in the DB to be sure that category and ordered_qty are available? Btw, to make a getSelect() work, you should do as follow : $myCollection->count(); echo $mycollection->getSelect()->__toString(); Jun 11, 2014 at 15:15
  • Ah! Thanks for the getSelect tip, I now see that it does include category in the query. I know for a fact that ordered_qty is available, I see it being returned. With the SQL query I will be able to tweak this further, thanks for the help so far! Jun 11, 2014 at 15:21
  • Haha wow. If I only add $reports->count(); to my test code, it suddenly works! If I leave it out, it doesn't. Do you have a clue why? The rest of my code is simply doing $reports->getData() in a foreach loop. Jun 11, 2014 at 15:23
  • for some reasons beyond my comprehension (and because I didn't really look into Magento's core), when you getSelect on a collection without count'ing first, then the setOrder won't be visible. Jun 12, 2014 at 8:39

2 Answers 2


Try building your reports like this:

$category = Mage::getModel('catalog/category')->load({category_id});
$reports = Mage::getResourceModel('reports/product_collection')
             ->setOrder('ordered_qty', 'desc');

I mean use addCategoryFilter after addOrderQty. in the method addOrderQty there is this line $this->getSelect()->reset() that seams to remove your category filter.

  • for single category code is working fine, I have tried addAttributeToFilter('category_ids',array('finset'=>'{categor1},{category2}')) but it is not working please suggest for multiple categories filter Jul 7, 2017 at 8:46
  • I don't think It's possible for 2 categories.
    – Marius
    Jul 7, 2017 at 9:18
  • can we filter this using a product attribute value Sep 7, 2018 at 9:06
  • Not sure. I wrote this 4+ years ago. BUt you can try with addAttributeToFilter
    – Marius
    Sep 7, 2018 at 9:33

Using custom query : Get the bestselling product from last 15 days with multiple categories

$startDate = date('Y-m-d', strtotime(' - 15 days'));
$endDate = date('Y-m-d');
$categories = "{{Category1}},{{category2}},{{category3}}";
// example $categories = "5,6,7";           

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$sql = "SELECT Sum(order_items.qty_ordered) AS `ordered_qty`, 
       `order_items`.`name`         AS `order_items_name`, 
       `order_items`.`product_id`   AS `entity_id`, 
       `cat_index`.`position`       AS `cat_index_position`,
       `stock_table`.`qty`          AS `quantity`
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' 
                  AND ( `order`.created_at BETWEEN '".$startDate."' AND '".$endDate."' 
       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_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 IN(".$categories.")
       INNER JOIN `cataloginventory_stock_item` AS `stock_table`
               ON stock_table.product_id = e.entity_id  
WHERE  ( parent_item_id IS NULL ) AND stock_table.qty > 0  
GROUP  BY `order_items`.`product_id` 
HAVING ( Sum(order_items.qty_ordered) > 0 ) 
LIMIT  6"; 
$orderCollection = $connection->fetchAll($sql); 

Hope this will help someone!

Your Answer

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

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