Can't filter 'reports/product_collection' on category

Question

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')
                 ->addAttributeToSelect('*')
                 ->addCategoryFilter($category)
                 ->addOrderedQty()
                 ->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?

Answer 1

Try building your reports like this:

$category = Mage::getModel('catalog/category')->load({category_id});
$reports = Mage::getResourceModel('reports/product_collection')
             ->addAttributeToSelect('*')
             ->addOrderedQty()
             ->addCategoryFilter($category)
             ->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.

Answer 2

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`, 
       `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`, 
       `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 ) 
ORDER  BY RAND() 
LIMIT  6"; 
$orderCollection = $connection->fetchAll($sql); 

Hope this will help someone!