Collection filtering with OR and inner AND

Question

can somebody tell what is the best way to filter a collection like this:

where email='[email protected]' OR (firstname='John' AND lastname='Doe' and city='Boston')

I got a working version with this code:

$orderCollection = Mage::getModel('sales/order')->getCollection();
$orderCollection->join(
    array(
        'order_address' =>'sales/order_address'),
        'order_address.parent_id=main_table.entity_id AND order_address.address_type="billing"',
        array('firstname','lastname','city')
     )
;
$orderCollection->addFieldToSearchFilter('customer_email', array('EQ'=>'[email protected]'));   
$orderCollection->addFieldToSearchFilter('customer_firstname', 'John');
$orderCollection->addAttributeToFilter('customer_lastname', 'Doe');       
$orderCollection->addAttributeToFilter('city', 'Boston');

The SQL Statement is correct:

SELECT `main_table`.*, `order_address`.`firstname`, `order_address`.`lastname`, `order_address`.`city` 
FROM `sales_flat_order` AS `main_table`
INNER JOIN `sales_flat_order_address` AS `order_address` ON order_address.parent_id=main_table.entity_id AND order_address.address_type="billing" 
WHERE (((customer_email = '[email protected]'))) OR (customer_firstname = 'John') AND (customer_lastname = 'Dow') AND (`order_address`.city = 'Boston')

I think this solution is not good because it depends on the order of the terms and the rule "AND before OR". I would like to know a solution, that explicitly sets the correct parenthesis like this:

WHERE (customer_email = '[email protected]') OR ((customer_firstname = 'Sascha') AND (customer_lastname = 'Wohlgemuth') AND (city = 'Berlin'))

Am I pedantic here? Does it even matter? I think of sitations where you can't have a correct order of terms and really need to set the parenthesis the right way. What do you think?

Answer 1

I answered a similar question recently:

Complexe filter in a collection

Where the underlying Zend_Db_Select object gives you a finer degree of control over your statement structure. So, in this case, you could do something like this:

$orderCollection = Mage::getModel('sales/order')->getCollection();
$orderCollection->join(
    array(
        'order_address' => 'sales/order_address'),
        'order_address.parent_id = main_table.entity_id AND order_address.address_type = "billing"',
        array('firstname', 'lastname', 'city')
     )
;

$orderCollection->getSelect()
    ->where(new Zend_Db_Expr("
        (customer_email = '[email protected]') 
        OR (
            (customer_firstname = 'Sascha') 
            AND (customer_lastname = 'Wohlgemuth') 
            AND (city = 'Berlin')
        )
    "));

Note in the referenced answer that you can also pass in your bindings to the where method.

Are you being pedantic? I don't think so. From time to time there will be a need for specifically ordered conditions on your clauses. This is one of those times.