How to filter an order collection by cancelled items?

Question

Is it possible to filter an Mage_Sales_Model_Order collection by item ids?

I need a collection but there are some items inside that I don't want.

I tried it with:

$canceledItemIds = array(101,102,103);
    $orderModel = Mage::getModel('sales/order')->load($order->getId());
    $orderModel->getCollection()
               ->join(array('item' => 'sales/order_item'), 'main_table.entity_id = item.order_id')
               ->addFieldToFilter('item_id', $canceledItemIds);

But that had no effect.

Answer 1

Since the ID's are an array you need an IN statement

$canceledItemIds = array(101,102,103);
    $orderModel = Mage::getModel('sales/order')->load($order->getId());
    $orderModel->getCollection()
               ->join(array('item' => 'sales/order_item'), 'main_table.entity_id = item.order_id')
               ->addFieldToFilter('item_id', array('IN' => $canceledItemIds));

for remove multiple/duplicated row for an order

$orderModel->getSelect()->group('main_table.entity_id')

Answer 2

Hi remove load function from ->load($order->getId()); for getting order-collection filter by item id.AS sander say need to use in at collection condition.

Mage::getModel('sales/order')->load($order->getId()) wil give you particular order it object and does not provide all order collection. More about filter see at .

For getting a collection please try with format

Mage::getModel('modelsPrefix/modelname')->getCollection();

thus you can get order collection by

Mage::getModel('sales/colllection')->getCollection();





$canceledItemIds = array(101,102,103)   
 $orderModel = Mage::getModel('sales/order')
$orderModel->getCollection()
               ->join(array('item' => 'sales/order_item'), 'main_table.entity_id = item.order_id')
               ->addFieldToFilter('item_id',array('in', $canceledItemIds));

then remove the duplicate and multiple rows for each order when order have multiple items use group by

$orderModel->getSelect()->group('main_table.entity_id');

Note: item_id give the order item id not product id.Forgetting product id try use field product_id.See at sale_flat_order_item table at db