3

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

where email='john.doe@gmail.com' 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'=>'john.doe@gmail.com'));   
$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 = 'john.doe@gmail.com'))) 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 = 'sascha.wohlgemuth@gmail.com') 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?

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 = 'sascha.wohlgemuth@gmail.com') 
        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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.