Filtering Magento Collections by COND1 AND (COND2 OR COND3)

Question

How do you filter the Magento Sales Order Collection by attribute1 = value1 AND (attribute2 = value2 OR attribute3 = value2)? I can write WHERE {COND1} AND {COND2} OR {COND3}, but I can't group AND ({COND2} OR {COND3})

First of all, this is not a duplicate AFAIK, I've seen this and it works great in version 1.3.2, but not in Enterprise Edition 1.11.1. Here's what I'm trying to do... get the Magento orders that were created or updated in a defined date range that have a status of 'processing'. Here is the code that works in previous versions, but not in mine:

$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'),
        ),
    ));

This is the SQL that it's generating, and the resulting error:

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

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

In digging through the code, I found the addFieldToFilter function in lib/Varien/Data/Collection/Db.php

/** 
 * Add field filter to collection
 *
 * @see self::_getConditionSql for $condition
 * @param string $field
 * @param null|string|array $condition
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
public function addFieldToFilter($field, $condition=null)
{   
    $field = $this->_getMappedField($field);
    $this->_select->where($this->_getConditionSql($field, $condition),
        null, Varien_Db_Select::TYPE_CONDITION);
    return $this;
}   

// **********************************************
// ** Different from addFieldToFilter in 1.3.2 **
// **********************************************

/** 
 * Add field filter to collection
 *
 * If $attribute is an array will add OR condition with following format:
 * array(
 *     array('attribute'=>'firstname', 'like'=>'test%'),
 *     array('attribute'=>'lastname', 'like'=>'test%'),
 * )
 *
 * @see self::_getConditionSql for $condition
 * @param string|array $attribute
 * @param null|string|array $condition
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
public function addFieldToFilter($field, $condition=null)
{   
    $field = $this->_getMappedField($field);
    $this->_select->where($this->_getConditionSql($field, $condition));
    return $this;
}       

It looks like addFieldToFilter used to accept the first parameter as an array, but now it must be a string... Interesting, so I tried the following with zero luck...

$orderIds = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('status', 'processing')
    ->addFieldToFilter('attribute', 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'),
        ),
    ));
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table`
WHERE (status = 'processing')
AND ((
    (attribute >= '2012-06-13 17:52:01' AND attribute <= '2012-06-15 17:52:01')
 OR (attribute >= '2012-06-13 17:52:01' AND attribute <= '2012-06-15 17:52:01')
))

I know I can do it by manipulating the SQL, but I really want to know how to do it "the Magento way" if there is one...

By the way, I've also tried using addAttributeToFilter and the error message is "Cannot determine field name".

---

UPDATE

I came upon two functions in Mage_Sales_Model_Resource_Order_Collection that look semi-promising, but they're still not quite what I want.

/**
 * Add field search filter to collection as OR condition
 *
 * @see self::_getConditionSql for $condition
 *
 * @param string $field
 * @param null|string|array $condition
 * @return Mage_Sales_Model_Resource_Order_Collection
 */
public function addFieldToSearchFilter($field, $condition = null)
{
    $field = $this->_getMappedField($field);
    $this->_select->orWhere($this->_getConditionSql($field, $condition));
    return $this;
}

/**
 * Specify collection select filter by attribute value
 *
 * @param array $attributes
 * @param array|integer|string|null $condition
 * @return Mage_Sales_Model_Resource_Order_Collection
 */
public function addAttributeToSearchFilter($attributes, $condition = null)
{
    if (is_array($attributes) && !empty($attributes)) {
        $this->_addAddressFields();

        $toFilterData = array();
        foreach ($attributes as $attribute) {
            $this->addFieldToSearchFilter($this->_attributeToField($attribute['attribute']), $attribute);
        }
    } else {
        $this->addAttributeToFilter($attributes, $condition);
    }

    return $this;
}

When I update my code I get very close to my desired result, however what I really want is to have CONDITION1 AND (CONDITION2 OR CONDITION3)

$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'),
        ),
    ));

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')

Answer 1

Note that FLAT TABLE and EAV collections use a different syntax!

Adding OR conditions to EAV collections

Note that when specifying OR conditions for the same attribute the syntax is different then if you want to use two different attributes.

Use $collection->load(true) to test and see the specified filter as SQL (I find it easier to understand that way).

For EAV collections use addAttributeToFilter() or addFieldToFilter(), but the first can take an optional third $joinType argument.

How To add an OR filter for ONE attribute on EAV collections: the attribute code is the first argument and the conditions are the second.

Eg: $col->addFieldToFilter('name', array(array('like' => 'M%'), array('like' => '%O'))); // (get items whose name starts with M OR ends with O)

How To add an OR filter for DIFFERENT attributes on EAV collections: only pass one argument, an array of conditions with attributes.

Adding OR conditions to FLAT TABLE collections

Specifying an OR filter with a single attribute on a flat table collection is the same as on a EAV collection.

Eg: $col->addFieldToFilter('name', array(array('like' => 'M%'), array('like' => '%O'))); // get items whose name start with M OR end with O

To join multiple attributes in an OR condition the syntax is different from EAV collections (NOTE: this is BROKEN in Magento 1.6)

Eg: $col->addFieldToFilter(array('weight', 'name'), array(array('in' => array(1,3)), array('like' => 'M%')));

The first attribute is mapped to the first condition, the second attribute is mapped to the second condition, etc

Since this is broken in 1.6 we are left with the possibility to specify simple equality conditions for multiple attributes using addFilter()

Eg: $col->addFilter('weight', 1, 'or')->addFilter('weight', 2, 'or')->addFilter('sku', 'ABC', 'or'); // weight is 1 or 2 or sku is ABC

You can always use $collection->getSelect()->orWhere(…), too, but you have to watch out for cross RDBMS compatibility

Note: this is a cross post of thwo things I posted ages ago on tumblr:
http://tweetorials.tumblr.com/post/10844018716/eav-collection-or-filters
http://tweetorials.tumblr.com/post/11102525164/flat-table-collection-or-filters

Answer 2

Here is the solution that I would rather not use, which modifies the SELECT statement via Zend_Db methods.

$orderIds = Mage::getModel('sales/order')->getCollection()
    ->getSelect();
$adapter = $orderIds->getAdapter();
$quotedFrom = $adapter->quote(
    $fromDate->toString('yyyy-MM-dd HH:mm:ss')
);
$quotedTo = $adapter->quote(
    $toDate->toString('yyyy-MM-dd HH:mm:ss')
);
$orderIds
    ->where('status = ?', 'processing')
    ->where(
        vsprintf(
            '(created_at >= %s AND created_at <= %s)'
          . ' OR (updated_at >= %s AND updated_at <= %s)',
            array(
                $quotedFrom, $quotedTo,
                $quotedFrom, $quotedTo,
            )
        )
    );