magento How to Filter Collections Handling ANDs and ORs in Filters
Example
When we query our data, we often need more than one filter to get the exact data set we are looking for. In SQL, we handle this with AND and OR clauses. We can achieve the same thing with collections.
To add an AND clause to your query, just simply add another method call. This will append the second filter to the original WHERE statement joining it with an AND.
Mage::getModel('catalog/product')->getCollection()
->addFieldToFilter('sku',array('like'=>'a%'))
->addFieldToFilter('sku',array('like'=>'%b'));
The resulting WHERE clause will look like this:
WHERE (e.sku like 'a%') AND (e.sku like '%b')
Now lets say we want all skus that start with 'a' OR end with 'b'. How do we add an OR clause? Thanks to Magento's collections, it is pretty straight forward. We add the filter as a second element in the filter array.
Mage::getModel('catalog/product')->getCollection()
->addFieldToFilter('sku', array(
array('like'=>'a%'),
array('like'=>'%b')
));
Now, the resulting WHERE clause will look like this:
WHERE (((e.sku like 'a%') or (e.sku like '%b')))
