Product collection based on the total sales of a product within a period

Question

What I need is the logic for creating a product collection based on the total sales of a product within a period.

So if a product costs 10 $ and I sold 540 I want the sorting to be based on the total sale 5400.

EDITED & SOLVED:

What I have so far is this:

$_productCollection = Mage::getResourceModel('catalog/product_collection')
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addStoreFilter()
        ->addPriceData()
        ->addTaxPercents()
        ->addUrlRewrite()
        ->setPageSize($limit);

    $_productCollection->getSelect()
        ->joinLeft(
            array('aggregation' => $_productCollection->getResource()->getTable('sales/bestsellers_aggregated_daily')),
            "e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$from}' AND '{$to}'",
            array('SUM(aggregation.qty_ordered) AS sold_quantity')
        )
        ->group('e.entity_id')
        ->order(array('sold_quantity DESC', 'e.created_at'));

    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($_productCollection);

But that gives me only a sorting based on the number of sold products. How to add the price multiplication to it?

Abdul · Answer 1 · 2016-01-19T08:19:43.750

Try this

<?php
  $_productCollection = Mage::getResourceModel('catalog/product_collection')
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addStoreFilter()
        ->addPriceData()
        ->addTaxPercents()
        ->addUrlRewrite()
        ->setPageSize($limit);

    $_productCollection->getSelect()
        ->joinLeft(
            array('aggregation' => $_productCollection->getResource()->getTable('sales/bestsellers_aggregated_daily')),
            "e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$from}' AND '{$to}'",
            array('total_sale ' => 'SUM(aggregation.product_price * aggregation.qty_ordered)')
        )
        ->group('e.entity_id')
        ->order(array('total_sale DESC', 'e.created_at'));

    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($_productCollection);
?>

Note : I have added one condition array('total_sale ' => 'SUM(aggregation.product_price * aggregation.qty_ordered)') in jointLeft

Hi Abdul, thanks for the hint. But it doesn't work. '[total_sale] => ' remains empty. — Bernhard Prange, Jan 19 '16 at 15:01

Product collection based on the total sales of a product within a period

1 Answers1