How to create a multi-condition in addAttributeToFilter?

I want result in a SQL query like this (image attached) :

WHERE ((`e`.`news_from_date` > '2013-09-12') OR (`e`.`news_to_date` < '2013-09-12'))
AND ((((`e`.`special_price` IS NULL))) OR (((`e`.`special_price` IS NOT NULL)) AND ((`e`.`special_from_date` < '2013-09-12') OR (`e`.`special_to_date` > '2013-09-12'))))

$collection->addAttributeToFilter('special_price', array('null'=>'special_price'), 'left');

Thank youenter image description here


3 Answers 3


You should be able to combine most of these techniques to create the query you want. For sales tables, you probably will use addFieldToFilter - but Zend_Db_Expr is likely the path of least resistance for you :

addAttributeToFilter :

According to the Magento Wiki : When creating parentheticals that have OR conditions you can do the following:

If an array is passed but no attribute code specified, it will be interpreted as a group of OR conditions that will be processed in the same way.

So, from that we can construct the following :

        array('attribute'=> 'someattribute','like' => 'value'),
        array('attribute'=> 'otherattribute','like' => 'value'),
        array('attribute'=> 'anotherattribute','like' => 'value'),

This will output a WHERE clause of the format :

WHERE ((someattribute LIKE 'value') OR (otherattribute LIKE 'value') OR (anotherattribute LIKE 'value'))

addFieldToFilter :

In the case where the model is directly linked to a DB table, the following is required for applying conditionals to the database column by name :

    array('title', 'content'),

Zend_Db_Expr :

For much more complicated constructs you can build your own where clause using Zend_Db_Expr. For instance :

$collection->getSelect()->where(new Zend_Db_Expr("(e.created_at > '2013-01-01 00:00:00' OR e.created_at <'2012-01-01 00:00:00)"));

Source :



  • Thanks for your reply, but the thing that I want is to make condition like: WHERE (CONDITION_1 OR (CONDITION_2 AND (CONDITION_3 OR CONDITION_4))). I already post the query that I want above. Commented Sep 13, 2013 at 3:17
  • 1
    I thought I made the point that you can use Zend_Db_Expr: $collection->getSelect()->where(new Zend_Db_Expr("(CONDITION_1 OR (CONDITION_2 AND (CONDITION_3 OR CONDITION_4)))"));
    – philwinkle
    Commented Sep 13, 2013 at 3:25
  • thanks philwinkle.base on your answer, this is sample what i did with addAttributeToFilter and AND and OR condition: pastebin.com/ZznxLAai Commented Dec 3, 2014 at 2:56
  • Is there a way to determine which attribute produced a result? I am searching a custom model collection across three fields (name, altnernate_name, description) and would like to know when a result is because of the description attribute.
    – pspahn
    Commented Jan 8, 2018 at 1:14

If you want to include AND with the combination of OR then

$colemanManufacturerSku = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToFilter('type_id', array('eq' => 'simple'))
    // Above condition will AND with following OR
                'attribute' => 'sku_1',
                'neq' => ''
                'attribute' => 'sku_2',
                'neq' => ''
                'attribute' => 'sku_3',
                'neq' => ''
  • 2
    +1 for your solution, but I need exactly opposite, i.e. WHERE (CONDITION_1 And CONDITION_2) OR (CONDITION_3 AND CONDITION_4))), Can you please help
    – Haris
    Commented Jan 16, 2018 at 18:42

Or condition using addFieldToFilter


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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