Multi Conditionals (OR and AND inside AND) in addAttributeToFilter
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
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' => ''
)
)
);