9

I am trying to find out how many times a product was ordered over the last two weeks. So far I have a loop getting the products.

    foreach($productCollection as $product){

    }

I assume I should be able to get the order details with this placed inside the loop

    $order_items = Mage::getResourceModel('sales/order_item_collection')

I am a little unsure about how to filter this. I know that it needs to be filtered by product id and also the order must have been made within the last 2 weeks.

What should the syntax look like for this query, please?

| improve this question | | | | |
  • Do you have access to MySQL to do a simple query or are you want to make some functionality in Magento? – brentwpeterson Sep 13 '13 at 14:14
  • Unfortunately I can't do a MySQL query. Essentially it's for functionality and it would be best if it worked using php only – develophper Sep 13 '13 at 14:17
21

Let's start with thinking about the SQL, not Magento (we'll get there later). I would write it as such (ignoring timezones for simplicity):

SELECT sku,SUM(qty_ordered) FROM sales_flat_order_item
WHERE created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()
GROUP BY sku

That query works (I tested). So, how would we go about writing this query in Magento?

  • Firstly, we see that it's entirely based on sales_flat_order_item - Magento has a special resource collection for this table; we can make use of that.
  • We see that it uses a SUM in one of the columns
  • WHERE has a BETWEEN clause - we can probably use Zend_Db_Expr to output our custom rolling 2-weeks timeframe.
  • Finally, it has a GROUP

Let's see if we can't put it together, then, with a quick call to reset to make sure that we only get the columns we define, and nothing else:

$query = Mage::getResourceModel('sales/order_item_collection');
$query->getSelect()->reset(Zend_Db_Select::COLUMNS)
        ->columns(array('sku','SUM(row_total)'))
        ->where(new Zend_Db_Expr('created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()'))
        ->group(array('sku'));

A simple echo of $query->getSelect() shows us that the query is formatted quite well:

SELECT `main_table`.`sku`, SUM(qty_ordered) FROM `sales_flat_order_item` AS `main_table` WHERE (created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()) GROUP BY `sku`

Other considerations:

In the future you may want to filter this report based on order status (joining to another table) or you may want to make sure that the time zones are accurate (right now it's reporting based on GMT).

Joining is simple:

->join(array('e' => 'sales_flat_order'),'main_table.order_id = e.entity_id')

But adding time zones can get complicated. Look into Mage_Core_Model_Date for some methods that convert timestamps to and from GMT. Also look into the the sales reports collection.

Hope that helps! Best of luck.

| improve this answer | | | | |
  • 1
    you're right. This way loading any Magento classes would be complete overkill – Sander Mangel Sep 13 '13 at 14:32
  • See my update - forgot to include what $query was! – philwinkle Sep 13 '13 at 14:34
  • Another update to substitute the dollar amount for the qty purchased. – philwinkle Sep 13 '13 at 16:44
  • Thanks a lot @philwinkle.. I have customized you solution in my requirement. Thank you once again. – Pavan Kumar Mar 13 '14 at 11:34
  • can get only configurable product means parent product not associated from order and count how many time it sold – Bhupendra Jadeja Jul 31 '15 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.