Magento filter order collection by product sku

Magento filter order collection by product sku

July 27, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento filter order collection by product sku

Filtering Magento Order Collection by Product SKU

As a senior developer working with e-commerce platforms, I often encounter situations where clients need to filter orders based on specific product attributes like SKUs. In this blog post, we’ll walk through the process of filtering a Magento order collection by product SKU in version 1.7.

Understanding the Magento Order Collection

First, let’s understand what you have already:

$orderCollection = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('status',
        array(
            'nin' => array(
                'new',
                'pending',
                'pending_payment',
                'holded',
                'canceled')
        ))
    ->addFieldToFilter('increment_id', array('gteq' => $last_order));

This code retrieves all order collections, filters them by status (excluding certain states), and ensures the increment ID is greater than or equal to a specified value. However, when you try to add a field filter for sku, Magento throws an error because the SKU isn’t directly available in the sales_order table.

Joining with the Order Items Collection

The key to solving this issue lies in joining the order collection with the order items collection. The sales_flat_order_item table contains the product SKU information linked to each order.

Here’s how you can achieve this:

$orderCollection = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('status',
        array(
            'nin' => array(
                'new',
                'pending',
                'pending_payment',
                'holded',
                'canceled')
        ))
    ->addFieldToFilter('increment_id', array('gteq' => $last_order))
    ->joinField('item_id',
        'sales/flat_order_item',
        'order_id',
        'entity_id',
        null,
        'left');

In this code, we use the joinField method to join the order collection with the order items collection on the order_id field. The null argument specifies that no additional conditions are applied for the join.

Adding the SKU Filter

Now that our collections are joined, we can add a filter for the product SKU:

$findSku = 'your_sku_here';
$orderCollection->addFieldToFilter('item_id.sku', array('like' => $findSku));

Here, item_id refers to the field from our joined collection (sales/flat_order_item), and we use the like operator to perform a fuzzy search for the SKU.

Conclusion

In this post, we explored how to filter Magento order collections by product SKUs using version 1.7. We discovered that joining with the order items collection is essential to access the SKU information.

Remember, when working with e-commerce platforms like Magento, it’s crucial to understand the database schema and the relationships between tables. Joining collections allows you to retrieve data from multiple sources in a single query, improving performance and providing more comprehensive results.

For more advanced topics on Magento development, I recommend checking out our blog at https://laravelcompany.com. We cover everything from best practices for Magento 2.x upgrades to optimizing your store’s performance.

Magento 1 Catalog Database Orders

Our network