Magento: How to filter a collection (sales/order) by a certain category?

Magento: How to filter a collection (sales/order) by a certain category?

September 12, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento: How to filter a collection (sales/order) by a certain category?

Filtering Magento Sales Orders by Category

Hi Rik,

Thanks for reaching out and sharing your code snippet. I understand you’re trying to filter all ordered items from a specific category in Magento. Let’s dive into the issue and find a solution.

The Problem: Incorrect Collection Selection

In your current approach, you’re directly fetching the sales order collection without applying any filters. This returns every single order regardless of its associated products or categories:

$salesCollection = Mage::getModel("sales/order")->getCollection();

The Solution: Filtering by Associated Products and Category ID

To achieve this, we need to filter orders based on the category ID of their associated products. Here’s how you can modify your code:

  1. First, load the desired category model:
$categoryId = 44; // Replace with your target category ID
$_category = Mage::getModel('catalog/category')->load($categoryId);
  1. Next, fetch all the associated products for that category:
$productsCollection = $_category->getProductCollection();
  1. Now, we’ll filter orders based on these product IDs. We need to join the sales_flat_order_item table with our collection and apply a condition:
$salesCollection = Mage::getModel("sales/order")
    ->getCollection()
    ->join('sales/quote_item', 'main_table.entity_id=quote_item.order_id')
    ->join('catalog/product', 'quote_item.product_id=entity_id')
    ->addFieldToFilter('product.sku', array('in' => $productsCollection->getAllIds()))
    ->addAttributeToSelect(array('*')); // Select all order attributes
  1. Finally, iterate through the filtered orders:
foreach ($salesCollection as $order) {
    echo "Order ID: " . $order->getId() . PHP_EOL;
    foreach ($order->getAllItems() as $item) {
        echo "Product SKU: " . $item->getSku() . PHP_EOL;
        // Add your processing logic here
    }
}

In this solution, we first fetch all product IDs belonging to the desired category. Then, we join the order item table with both the quote item and product tables. By applying the addFieldToFilter condition, we ensure only orders containing products from that specific category are selected.

Conclusion

Remember, when working with Magento, it’s crucial to understand how data is organized across multiple tables. In this case, the association between categories and orders isn’t direct; we need to navigate through product entities.

I recommend reading our guide on Magento Collection Filters for a deeper understanding of filtering techniques in Magento.

Please give this modified code a try. If you still face issues, feel free to reach out again. I’m here to help!

Best, [Your Name]

Catalog Database Themes Orders

Our network