Magento 1.6 – How to Get Total Quantity of Specific Product Ordered Between Two Dates

admincollection-filteringmagento-1.6orders

I have a requirement to get the total number of ordered items for a specific product ID during a specific time period.

I.e. – Total number of Product 123 ordered between 1st June 2014 and 30st June 2014.

I'm looking for the best way to do this.

Initially, I looked at reports/product_collection but it seems slow compared to sales/order_item_collection from tests I've carried out (simple execution time).

The code I have at the moment is (with $week being a period to search through, and $row being a reference to an internal table where product_id is stored):

$collection = Mage::getResourceModel('sales/order_item_collection')
    ->addAttributeToFilter('product_id', array('eq' => $row->getProductId()))
    ->addAttributeToFilter(
        'created_at', array("gt" => array(
            $week["from"]
         )
         )
    )
    ->addAttributeToFilter(
        'created_at', array("lt" => array(
             $week["to"]
         )
         )
    );

However, then I have to loop through the resulting collection, adding the quantities together to get the figure I want.

 $qtySold = 0;
 foreach($collection as $orderItem) {
     $qtySold += $orderItem->getQtyOrdered();
 }

I feel like I should be able to do this in the original collection call by using something like addExpressionAttributeToSelect (but this doesn't work on the Mage_Sales_Model_Resource_Order_Item_Collection object).

Any thoughts on the best, most Magento (currently the store is running 1.6) way to do this? It's worth pointing out (I guess) that the PHP counter works, but it just doesn't feel right, especially as the collection could have thousands of orders in it.

Best Answer

For anyone stumbling over this in the future, I've gone for the following code as the most (to me) sensible version:

$collection = Mage::getResourceModel('sales/order_item_collection');
$collection->getSelect()->reset(Zend_Db_Select::COLUMNS)
    ->columns(array('SUM(qty_ordered) as order_quantity'))
    ->where(sprintf("product_id = %s", $row->getProductId()))
    ->where(sprintf('created_at > "%s" AND created_at < "%s"', $week["from"], $week["to"]));

I'm yet to figure out if it would be quicker to grab all my date ranges at once and work through them in PHP, or if it's quicker like this creating a separate block for each week.

Related Topic