How to sort products in Magento by best selling from the last month, mysql query based style?

Question

I managed to overwrite the Mage/Catalog/Model/Config.php and make a method that adds a new sorting option called "Sort By Top Sellers".

I also added a where statement where I can get data from last month but the collection doesn't output all the products, only the ones buyed in the last month (I need to sort all the products, not only the ones from last month).

The query outputs correct without the where statement.

Any idea on how to solve this ?

    public function sortByTopSelling($dir){
    $today = time();
    $last = $today - (60*60*24*30);
    $from = date("Y-m-d H:i:s", $last);
    $to = date("Y-m-d H:i:s", $today);

    $this->getSelect()->joinLeft('sales_flat_order_item AS order_item','e.entity_id = order_item.product_id',
    'SUM(order_item.qty_ordered) AS ordered_qty')->where('`order_item`.`created_at` > "'.$from.'"')->group('e.entity_id')->order('ordered_qty DESC');

}

GoatHater GoatHater · Accepted Answer · 2016-07-25T14:12:04

Debated a while between replying as a comment or an answer because I am not 100% sure on the Magento Code.

MYSQL:

select e.entity_id, SUM(order_item.qty_ordered) AS ordered_qty
from  magento.catalog_product_entity as e
left join magento.sales_flat_order_item AS order_item on e.entity_id = order_item.product_id and order_item.created_at >  DATE_FORMAT(NOW() ,'%Y-%m-01')
group by e.entity_id
order by ordered_qty DESC;

I don't have a reliable way to test the Magento code, but it should be something along the lines of:

 $this->getSelect()->joinLeft('sales_flat_order_item AS order_item',"e.entity_id = order_item.product_id and order_item.created_at > $from",
    'SUM(order_item.qty_ordered) AS ordered_qty')->group('e.entity_id')->order('ordered_qty DESC');

The other way to do it would be to add or order_item.created_at is null to the where, but this will take longer to run than another join condition, and in my Magento DB, the query already takes a staggering 90 seconds, but my tables are massive, it may work for your implementation.

How to sort products in Magento by best selling from the last month, mysql query based style?

1 Answers