how do I sort products by most sold in desc order based on reports?

Question

I would like to sort products in a category based on the most sold qty by using reports. I tried below code but it's only showing products that are sold but not unsold products. I want to know how do I get both sold and unsold products in the product collection sort by desc order. Thanks

    if (Mage::helper('abc_xyz')->displayMostSoldProducts()) {               
    $collection = Mage::getResourceModel('reports/product_collection')
    ->addAttributeToSelect('*')
    ->addOrderedQty()               
    ->addCategoryFilter($category)
    ->setOrder('ordered_qty', 'desc');
}

Mageworx Mageworx · Accepted Answer · 2016-01-13T15:05:01

When sending a query to the product collection, you are using the standard methods. They, in turn, rewrite the Select and make it use the data from the sales_flat_quote_item table. As you probably know, this table doesn't store data on the sold products.

Hence, you need to rewrite the Select the way you want to have it. It should be something like this:

$collection = Mage::getResourceModel('reports/product_collection');
$select = $collection->getSelect();
$select->joinLeft(
    array('sfoi' => $collection->getTable('sales/order_item')),
    '`e`.`entity_id` = `sfoi`.`product_id` ',
    array(
        'ordered_qty' => 'SUM(sfoi.qty_ordered)',
        'order_items_name' => 'sfoi.name',
        'entity_id' => 'sfoi.product_id',
    ))
->where('sfoi.parent_item_id IS NULL')
->group('e.entity_id');

In my case, I've taken the collection itself and added all the necessary info.

As a result, I've got this: https://gyazo.com/4a0b02298173f2c9356b7d7df783c0ff

That includes all unsold products: https://gyazo.com/24859373f52d31dba50560a02e0d0d79

But note, when solving this issue, you should take into account all peculiarities of your case. Unfortunately, there's no any standard method to implement this.

how do I sort products by most sold in desc order based on reports?

1 Answers