18

How to create a multi-condition in addAttributeToFilter.

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

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

41

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 :

$collection->addAttributeToFilter(
    array(
        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 :

$collection->addFieldToFilter(
    array('title', 'content'),
    array(
        array('like'=>'%$titlesearchtext%'), 
        array('like'=>'%$contentsearchtext%')
    )
)

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 :

https://stackoverflow.com/questions/5301231/addattributetofilter-and-or-condition-in-magentos-collection

https://stackoverflow.com/questions/3826474/magento-addfieldtofilter-two-fields-match-as-or-not-and/7851884#7851884

  • 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. – Andhi Irawan Sep 13 '13 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 Sep 13 '13 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 – Andhi Irawan Dec 3 '14 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 Jan 8 '18 at 1:14
4

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

$colemanManufacturerSku = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('sku')
    ->addAttributeToSelect('sku_1')
    ->addAttributeToSelect('sku_2')
    ->addAttributeToSelect('sku_3')
    ->addAttributeToFilter('type_id', array('eq' => 'simple'))
    // Above condition will AND with following OR
    ->addAttributeToFilter(
        array(
            array(
                'attribute' => 'sku_1',
                'neq' => ''
            ),
            array(
                'attribute' => 'sku_2',
                'neq' => ''
            ),
            array(
                '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 Jan 16 '18 at 18:42
1

Or condition using addFieldToFilter


$collection->addFieldToFilter
                (   
                    array('email','mobile'),
                    array($emaiId,$mobNum)
                );

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.