magentoHow to Filter Collections

Parameters

ParameterDetails
$addFieldToFilter($field, $condition = null){ string } The field we are adding to the filter.
$addFieldToFilter($field, $condition = null){ mixed } The definition of the filter we will use.
addAttributeToFilter($attr, $condition = null, $join = 'inner'){ string } The field we are adding to the filter.
addAttributeToFilter($attr, $condition = null, $join = 'inner'){ mixed } The definition of the filter we will use.
addAttributeToFilter($attr, $condition = null, $join = 'inner'){ ('inner','left') } The type of sql join to use when joining the EAV table.

Remarks

Filter Comparison Arguments

Magento also offers a flexible way of filtering using comparison operators as well. Here is a list of valid operators and their syntax:

All comparison arguments can be passed to the second parameter of either the addFieldToFielter() or addAttributeToFilter() methods.

$collection_of_products->addAttributeToFilter('visible',array("eq"=>1));
ComparisonArgument ArrayResulting SQL Snippet
Equalsarray("eq"=>$var)WHERE (`my_field` = $var)
Not Equalarray("neq"=>$var)WHERE (`my_field` != $var)
Likearray("like"=>$var)WHERE (`my_field` LIKE $var)
Not Likearray("nlike"=>$var)WHERE (`my_field` NOT LIKE $var)
Isarray("is"=>$var)WHERE (`my_field` IS $var)
Inarray("in"=>$var)WHERE (`my_field` IN($var))
Not Inarray("nin"=>$var)WHERE (`my_field` NOT IN($var))
Nullarray("null"=>true)WHERE (`my_field` IS NULL)
Not Nullarray("notnull"=>true)WHERE (`my_field` IS NOT NULL)
Greater Thanarray("gt"=>$var)WHERE (`my_field` > $var)
Less Thanarray("lt"=>$var)WHERE (`my_field` < $var)
Greater Than or Equalarray("gteq"=>$var)WHERE (`my_field` >= $var)
Less Than or Equalarray("lteq"=>$var)WHERE (`my_field` <= $var)
Find in Setarray("finset"=>array($var))WHERE (find_in_set($var,`my_field`)
From and Toarray("from"=>$var1, "to"=>$var2)WHERE (`my_field` >= $var1 AND `my_field` <= $var2)

Filtering Collections

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

Handling ANDs and ORs in Filters

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