Unexpected behavior in Magento collection filters used in loops

Question

I'm seeing an unexpected behavior in collections that maybe someone can enlighten me on (or maybe it's a bit of PHP I don't grok) (sorry for the length of this post, but I had to include sample code and results).

My goal was to write a report where I get an order and it's order items, then go to the invoice and shipment data for the order and get the matching order item data from their items. I know that there is only one invoice and shipment for all orders, so even though Magento uses a 1-M relationship between orders and invoices/shipments, I can take advantage of it as if it were 1-1

I know that the items are all related using the order_item_id fields, so I tried to write a function that used the the following call -

$invoiceItem = $order
  ->getInvoiceCollection()
  ->getFirstItem()
  ->getItemsCollection()
  ->addFieldToFilter('order_item_id', $orderItem->getItemId())
  ->getFirstItem();

But that didn't results I expected, what I saw was the same invoice item returned regardless of the order item id used in the filter.

So, to try to understand the problem, I wrote the following small program to see how the queries where created.

<?php

require dirname(__FILE__).'/../app/Mage.php';    
umask(0);
Mage::app('default');

$orders = Mage::getResourceModel('sales/order_collection')
       ->addAttributeToSelect('*')
       ->addAttributeToFilter('state', 'processing') 
       ->addAttributeToSort('created_at', 'desc')
       ->addAttributeToSort('status', 'asc')
       ->load();

foreach ($orders as $order) {

    echo "\ngetting data for order id ". $order->getId();

    $items = $order->getAllItems();
    $invoice = $order->getInvoiceCollection()->getFirstItem();

    foreach ($items as $orderItem) {

        echo "\n\ngetting data for order item id ". $orderItem->getItemId();    
        $invoiceItems = $order
                   ->getInvoiceCollection()
                   ->getFirstItem()
                   ->getItemsCollection()
                   ->addFieldToFilter('order_item_id', $orderItem->getItemId());    
        echo "\n".$invoiceItems->getSelect();    
    }
    die;  //just quit after one iteration
}

The output from this program was the following -

getting data for order id 7692

getting data for order item id 20870
SELECT `main_table`.* FROM `sales_flat_invoice_item` AS `main_table` WHERE (parent_id = '7623') AND (order_item_id = '20870')

getting data for order item id 20871
SELECT `main_table`.* FROM `sales_flat_invoice_item` AS `main_table` WHERE (parent_id = '7623') AND (order_item_id = '20870') AND (order_item_id = '20871')

getting data for order item id 20872
SELECT `main_table`.* FROM `sales_flat_invoice_item` AS `main_table` WHERE (parent_id = '7623') AND (order_item_id = '20870') AND (order_item_id = '20871') AND (order_item_id = '20872')

As you can see, every time though the loop another "AND (order_item_id =" was added for each item Id that I was filtering on. I thought that every time though the loop, I'd be getting a fresh version of the collection from using $order->getInvoiceCollection().

So, can anyone tell me what's going wrong in my sample code and educate me on the correct way to do this?

Thanks!

Manipulating $order alters its contents. I'm guessing if you recast $order each iteration (not necessarily efficient) it would work. Inside the foreach|$orderItem, try $order = Mage::getModel('sales/order')->load($order->getId()). If that works you should get some insight from there. — pspahn

benmarks benmarks · Accepted Answer · 2012-12-07T13:51:08

Regarding your business question: need more info. Is the goal to generate a collection with order item objects which are EACH aware of invoice and shipment details? It seems like there are rendering concerns getting pushed into modeling concerns.

Regarding the select statement question: Varien collections have an optimization which prevents them from accessing the storage backend more than once. This standard behavior is achieved in DB collection instances by setting the _isCollectionLoaded property to true.

In your case, the invoice collection instance is created via the order instance stored in a protected property, and immediately load()ed via IteratorAggregate (invoked via foreach). Because you are using the same order object instance in each iteration, you are dealing with this loaded invoice collection instance and are effectively calling addFieldToFilter(/* next order id */) with each iteration, resulting in the ever-expanding WHERE clause. This specific optimization can easily be worked around by calling $order->reset(). This brings us back to the salient issue though, which is the need to better understand the goal and (likely) use a custom collection or to manipulate a collection to join in the specific data that you need.

Unexpected behavior in Magento collection filters used in loops

1 Answers