addFilter vs addFieldToFilter

Magento collection has two methods for filtering bellow different

1. Varien_Data_Collection_Db::addFieldToFilter

addFieldToFilter($field, $condition=null)

The first parameter of addFieldToFilter is the attribute you wish to filter by. The second is the value you’re looking for. Here’s we’re adding a sku filter for the value n2610.

The second parameter can also be used to specify the type of filtering you want to do. This is where things get a little complicated, and worth going into with a little more depth.

So by default, the following

$collection_of_products->addFieldToFilter('sku','n2610'); 

is (essentially) equivalent to

WHERE sku = "n2610"

Take a look for yourself. Running the following

public function testAction()
{
    var_dump(
    (string) 
    Mage::getModel('catalog/product')
    ->getCollection()
    ->addFieldToFilter('sku','n2610')
    ->getSelect());
}

will yield

SELECT `e`.* FROM `catalog_product_entity` AS `e` WHERE (e.sku = 'n2610')'

Keep in mind, this can get complicated fast if you’re using an EAV attribute. Add an attribute

var_dump(
(string) 
Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addFieldToFilter('meta_title','my title')
->getSelect()
);

and the query gets gnarly.

SELECT `e`.*, IF(_table_meta_title.value_id>0, _table_meta_title.value, _table_meta_title_default.value) AS `meta_title` 
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_product_entity_varchar` AS `_table_meta_title_default` 
    ON (_table_meta_title_default.entity_id = e.entity_id) AND (_table_meta_title_default.attribute_id='103') 
    AND _table_meta_title_default.store_id=0        
LEFT JOIN `catalog_product_entity_varchar` AS `_table_meta_title` 
    ON (_table_meta_title.entity_id = e.entity_id) AND (_table_meta_title.attribute_id='103') 
    AND (_table_meta_title.store_id='1') 
WHERE (IF(_table_meta_title.value_id>0, _table_meta_title.value, _table_meta_title_default.value) = 'my title')

Not to belabor the point, but try not to think too much about the SQL if you’re on deadline.

Other Comparison Operators I’m sure you’re wondering “what if I want something other than an equals by query”? Not equal, greater than, less than, etc. The addFieldToFilter method’s second parameter has you covered there as well. It supports an alternate syntax where, instead of passing in a string, you pass in a single element Array.

The key of this array is the type of comparison you want to make. The value associated with that key is the value you want to filter by. Let’s redo the above filter, but with this explicit syntax

public function testAction()
{
    var_dump(
    (string) 
    Mage::getModel('catalog/product')
    ->getCollection()
    ->addFieldToFilter('sku',array('eq'=>'n2610'))
    ->getSelect()
    );          
}

Calling out our filter

addFieldToFilter('sku',array('eq'=>'n2610'))

As you can see, the second parameter is a PHP Array. Its key is eq, which stands for equals. The value for this key is n2610, which is the value we’re filtering on.

Magento has a number of these english language like filters that will bring a tear of remembrance (and perhaps pain) to any old perl developers in the audience.

Listed below are all the filters, along with an example of their SQL equivalents.

array("eq"=>'n2610')
WHERE (e.sku = 'n2610')

array("neq"=>'n2610')
WHERE (e.sku != 'n2610')

array("like"=>'n2610')
WHERE (e.sku like 'n2610')

array("nlike"=>'n2610')
WHERE (e.sku not like 'n2610')

array("is"=>'n2610')
WHERE (e.sku is 'n2610')

array("in"=>array('n2610'))
WHERE (e.sku in ('n2610'))

array("nin"=>array('n2610'))
WHERE (e.sku not in ('n2610'))

array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)

array("null"=>'n2610')
WHERE (e.sku is NULL)

array("gt"=>'n2610')
WHERE (e.sku > 'n2610')

array("lt"=>'n2610')
WHERE (e.sku < 'n2610')

array("gteq"=>'n2610')
WHERE (e.sku >= 'n2610')

array("moreq"=>'n2610') //a weird, second way to do greater than equal
WHERE (e.sku >= 'n2610')

array("lteq"=>'n2610')
WHERE (e.sku <= 'n2610')

array("finset"=>array('n2610'))
WHERE (find_in_set('n2610',e.sku))

array('from'=>'10','to'=>'20')
WHERE e.sku >= '10' and e.sku <= '20'

Most of these are self explanatory, but a few deserve a special callout

in, nin, find_in_set The in and nin conditionals allow you to pass in an Array of values. That is, the value portion of your filter array is itself allowed to be an array.

array("in"=>array('n2610','ABC123')
WHERE (e.sku in ('n2610','ABC123'))

notnull, null The keyword NULL is special in most flavors of SQL. It typically won’t play nice with the standard equality (=) operator. Specifying notnull or null as your filter type will get you the correct syntax for a NULL comparison while ignoring whatever value you pass in

array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)

from - to filter This is another special format that breaks the standard rule. Instead of a single element array, you specify a two element array. One element has the key from, the other element has the key to. As the keys indicated, this filter allows you to construct a from/to range without having to worry about greater than and less than symbols

public function testAction
{
        var_dump(
        (string) 
        Mage::getModel('catalog/product')
        ->getCollection()
        ->addFieldToFilter('price',array('from'=>'10','to'=>'20'))
        ->getSelect()
        );                      
}

The above yields

WHERE (_table_price.value >= '10' and _table_price.value <= '20')'

AND or OR, or is that OR and AND? Finally, we come to the boolean operators. It’s the rare moment where we’re only filtering by one attribute. Fortunately, Magento’s Collections have us covered. You can chain together multiple calls to addFieldToFilter to get a number of “AND” queries.

function testAction()
{
        echo(
        (string) 
        Mage::getModel('catalog/product')
        ->getCollection()
        ->addFieldToFilter('sku',array('like'=>'a%'))
        ->addFieldToFilter('sku',array('like'=>'b%'))
        ->getSelect()
        );                                  
}

By chaining together multiple calls as above, we’ll produce a where clause that looks something like the the following

WHERE (e.sku like 'a%') AND (e.sku like 'b%')

To those of you that just raised your hand, yes, the above example would always return 0 records. No sku can begin with BOTH an a and a b. What we probably want here is an OR query. This brings us to another confusing aspect of addFieldToFilter’s second parameter.

If you want to build an OR query, you need to pass an Array of filter Arrays in as the second parameter. I find it’s best to assign your individual filter Arrays to variables

public function testAction()
{
        $filter_a = array('like'=>'a%');
        $filter_b = array('like'=>'b%');
}

and then assign an array of all my filter variables

public function testAction()
{
        $filter_a = array('like'=>'a%');
        $filter_b = array('like'=>'b%');
        echo(
        (string) 
        Mage::getModel('catalog/product')
        ->getCollection()
        ->addFieldToFilter('sku',array($filter_a,$filter_b))
        ->getSelect()
        );
}

In the interest of being explicit, here’s the aforementioned Array of filter Arrays.

array($filter_a,$filter_b)

This will gives us a WHERE clause that looks something like the following

WHERE (((e.sku like 'a%') or (e.sku like 'b%')))

2. Varien_Data_Collection::addFilter

 addFilter($field, $value, $type = 'and')

addFilter() only allows filtering a single field by a single value and a type. $type can be any of:

1. "and" (default) - adds AND $field=$value to the WHERE clause
2. "or" - adds "OR $field=$value to the WHERE clause

See More Details

OK, let's examine them. The first difference is that addFilter() is more generic and not database specific. It's also used by Varien_Directory_Collection to filter by file name. But for this answer I am going to focus on Varien_Data_Collection_Db.

They methods have a different signature, where addFilter seems to be less flexible, but you'll see that it has its advantages as well:

1. addFieldToFilter()

/**
 * Add field filter to collection
 *
 * @see self::_getConditionSql for $condition
 *
 * @param   string|array $field
 * @param   null|string|array $condition
 *
 * @return  Mage_Eav_Model_Entity_Collection_Abstract
 */
public function addFieldToFilter($field, $condition = null)

Parameters

addFieldToFilter() can take an array of fields with an array of conditions, or a single field with a single condition:

• addFieldToFilter('field', 'value')

  Results in: field=value

• addFieldToFilter(['field1', 'field2'], ['value1', 'value2']);

  Results in: field1=value1 OR field2=value2

Each condition can be:

• a single scalar value (like 'value1' and 'value2' above)
• an array in the form [ operator => value ]
• a Zend_Db_Expr object
• an array of conditions which are combined with "OR" (yes, that's recursive)

This, especially the "operator => value" syntax is documented in the code at Varien_Db_Adapter_Pdo_Mysql::prepareSqlCondition() - remember this, I look them up quite often:

 * If $condition integer or string - exact value will be filtered ('eq' condition)
 *
 * If $condition is array - one of the following structures is expected:
 * - array("from" => $fromValue, "to" => $toValue)
 * - array("eq" => $equalValue)
 * - array("neq" => $notEqualValue)
 * - array("like" => $likeValue)
 * - array("in" => array($inValues))
 * - array("nin" => array($notInValues))
 * - array("notnull" => $valueIsNotNull)
 * - array("null" => $valueIsNull)
 * - array("moreq" => $moreOrEqualValue)
 * - array("gt" => $greaterValue)
 * - array("lt" => $lessValue)
 * - array("gteq" => $greaterOrEqualValue)
 * - array("lteq" => $lessOrEqualValue)
 * - array("finset" => $valueInSet)
 * - array("regexp" => $regularExpression)
 * - array("seq" => $stringValue)
 * - array("sneq" => $stringValue)
 *
 * If non matched - sequential array is expected and OR conditions
 * will be built using above mentioned structure

There is additional undocumented feature in the from / to operator:

• with ['from' => $dateFrom, 'to' => $dateTo, 'date' => true] the $dateFrom and $dateTo values will be parsed as dates. They can be in any form that is accepted by Varien_Date::formatDate()
• if you need the date parsing feature but only to compare one of <= or >=, you can omit either 'from' or 'to'.
• 'datetime' => true is supposed to work too and include the time, not only the day, but there is a bug in Varien_Db_Adapter_Pdo_Mysql::_prepareSqlDateCondition() (missing $includeTimestamp parameter) which makes datetime work the same way as date. Both include the time. So if you need to compare by date only, add 00:00:00 to the from date and 23:59:59 to the to date.

Field mapping

The method uses field mapping. Field mappings can be defined in concrete collection classes to create alias field names. Here's an example from the product collection:

protected $_map = array('fields' => array(
    'price'         => 'price_index.price',
    'final_price'   => 'price_index.final_price',
    'min_price'     => 'price_index.min_price',
    'max_price'     => 'price_index.max_price',
    'tier_price'    => 'price_index.tier_price',
    'special_price' => 'price_index.special_price',
));

2. addFilter()

/**
 * Add collection filter
 *s
 * @param string $field
 * @param string $value
 * @param string $type and|or|string
 */
public function addFilter($field, $value, $type = 'and')

Parameters

addFilter() only allows filtering a single field by a single value and a type. $type can be any of:

• "and" (default) - adds AND $field=$value to the WHERE clause (of course with proper quoting)
• "or" - adds "OR $field=$value to the WHERE clause (ditto)
• "string" - adds AND $value to the WHERE clause (i.e. $value can be an arbitrary SQL expression)
• "public" - uses field mapping and _getConditionSql(), similar to addFieldToFilter(). This makes it almost as powerful, it's only missing the feature to add multiple filters for different fields combined with OR.

In Varien_Data_Collection_Db::_renderFilters() you can see how they are processed.

Extensibility

There is one important difference which is an advantage for addFilter(). It collects the filters to be applied in $this->_filters() and only adds them to the Zend_Db_Select query object right before loading the collection. addFieldToFilter() on the other hand manipulates the query object immediately.

This allows you to manipulate or remove filters that already have been added. The Varien collection does not have an interface for it, you have to implement this in your custom collection. There is a hook method _renderFiltersBefore() that you can override.