Filtering Magento Collections by COND1 AND (COND2 OR COND3)

Filtering Magento Collections by COND1 AND (COND2 OR COND3)

August 4, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Filtering Magento Collections by COND1 AND (COND2 OR COND3)

Filtering Magento Sales Order Collections

Introduction

When working with Magento collections, especially for filtering sales orders, you might encounter challenges when trying to apply complex conditions like COND1 AND (COND2 OR COND3). This post will guide you through the process of applying this filter in a Magento Enterprise Edition collection using an example of retrieving orders based on date range and status.

The Challenge

The user encountered an issue while trying to retrieve sales orders with specific criteria:

  • Orders created or updated within a defined date range
  • Status set to ‘processing’

The code that worked previously (in version 1.3.2) failed in the Enterprise Edition 1.11.1 environment:

$orderIds = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('status', 'processing')
    ->addFieldToFilter(array(
        array(
            'attribute' => 'created_at',
            'from'      => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
            'to'        => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
        ),
        array(
            'attribute' => 'updated_at',
            'from'      => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
            'to'        => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
        ),
    ));

The SQL Generation

The generated SQL revealed the issue:

SELECT `main_table`.* FROM `sales_flat_order` AS `main_table`
WHERE (status = 'processing') AND (Array = '')

The resulting error message was:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause'

Digging Deeper into the Code

Upon examining the lib/Varien/Data/Collection/Db.php file, we noticed a significant change in the addFieldToFilter function between versions:

Version 1.3.2

public function addFieldToFilter($field, $condition = null)
{
    // ...
}

Enterprise Edition 1.11.1

public function addFieldToFilter($field, $condition = null)
{
    // ...
}

In the newer version, the first parameter must be a string instead of an array, which caused the issue.

The Workaround

While exploring the Mage_Sales_Model_Resource_Order_Collection class, we found two promising functions: addFieldToSearchFilter and addAttributeToSearchFilter. These functions allow adding field or attribute filters as OR conditions.

$orderIds = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('status', 'processing')
    ->addAttributeToSearchFilter(array(
        array(
            'attribute' => 'created_at',
            'from'      => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
            'to'        => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
        ),
        array(
            'attribute' => 'updated_at',
            'from'      => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
            'to'        => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
        ),
    ));

The generated SQL came very close to the desired result:

SELECT `main_table`.*,
       `billing_o_a`.`firstname`,
       `billing_o_a`.`lastname`,
       `billing_o_a`.`telephone`,
       `billing_o_a`.`postcode`,
       `shipping_o_a`.`firstname`,
       `shipping_o_a`.`lastname`,
       `shipping_o_a`.`telephone`,
       `shipping_o_a`.`postcode`
FROM `sales_flat_order` AS `main_table`
LEFT JOIN `sales_flat_order_address` AS `billing_o_a`
    ON (main_table.entity_id = billing_o_a.parent_id AND billing_o_a.address_type = 'billing')
LEFT JOIN `sales_flat_order_address` AS `shipping_o_a`
    ON (main_table.entity_id = shipping_o_a.parent_id AND shipping_o_a.address_type = 'shipping')
WHERE (status = 'processing')
OR (created_at >= '2012-06-16 16:43:38' AND created_at <= '2012-06-18 16:43:38')
OR (updated_at >= '2012-06-16 16:43:38' AND updated_at <= '2012-06-18 16:43:38')

The Desired Result

As you can see, the generated SQL almost meets our requirement of CONDITION1 AND (CONDITION2 OR CONDITION3). However, it applies the conditions as OR conditions instead of combining them with an AND condition.

To achieve this behavior, we need to manipulate the SQL directly or use Magento’s built-in functions in a different way. Unfortunately, there is no straightforward method available within the Mage_Sales_Model_Resource_Order_Collection class to apply complex filters with mixed AND and OR conditions.

Conclusion

While working with Magento collections, especially when dealing with sales order filters, you might encounter limitations in applying complex conditions like CONDITION1 AND (CONDITION2 OR CONDITION3). In this case, the built-in functions provided by Magento do not offer a direct solution for combining AND and OR conditions within a single filter.

To resolve this issue, you can either:

  • Manipulate the SQL directly to achieve the desired result
  • Create your own custom collection class that extends Mage_Eav_Model_Entity_Collection_Abstract or Mage_Sales_Model_Resource_Order_Collection, and implement the required filtering logic

For more information on creating custom Magento collections, you can refer to the official documentation here.

By following these best practices and understanding the limitations of the built-in functions, you can effectively work with Magento collections and retrieve sales orders based on your specific requirements.

Database Orders Errors

Our network