Magento has a powerful set of methods to filter collections. Since there are two types of Objects that can be contained in collections, we must first determine which type of data we are working with before we can filter it. Magento implements a EAV data model for entities such as products and categories. There is a different set of methods to use if we are filtering a collection of EAV Objects.

In Magento, Orders are not stored as EAV Objects. This makes the orders collection a good example for filtering a basic collection.

$collection_of_orders = Mage::getModel('sales/order')->getCollection();
$collection_of_orders->addFieldToFilter('status','processing');

If we look at the products collection, we can see that the products are stored in an EAV data model. We can easily filter by EAV attributes as well.

$collection_of_products = Mage::getModel('catalog/product')->getCollection();
$collection_of_products->addAttributeToFilter('visible',1);

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')))