addFilter vs addFieldToFilter

Magento collection has two methods for filtering:

1. Varien_Data_Collection_Db::addFieldToFilter
2. Varien_Data_Collection::addFilter

Seems that both methods add where condition to Zend_Db_Select. And what advantages does addFilter bring? When should I use it instead of addFieldToFilter ?

Solutions

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%')))
  1. 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.

Similar questions

Select only reviews that have an image using addFieldToFilter
I'm currently doing the following query to get the reviews from the review table. It works well and get the reviews however I want to add a new filter. We have the normal setup but in review_detail we have a column called image, how can I filter the results to only get results where the image field has a value? I've tried the following but it retur...
addFieldToFilter() not considering associated products
I'm using below code to get product collection, Using ->addFieldToFilter('size', array('eq' => '130')) i'm trying to filter product collection by it's size. As a result i'm getting empty result coz, my configurable product don't have attribute size I've tried to include child product collection by but not working, did anyone know how to consi...
AddFieldToFilter And inside OR condition
I want to achieve the following query in magento custom model. How to achieve this in magento with AddFieldToFilter ? Not AddAttributeToFilter.
How to addFieldToFilter a double number
I have tried this way but still does not work If I use the word "like" it works fine, but I just wanted to get the exact value that I entered. Thanks.
magento customer module add new table and config show error "Fatal error: Call to a member function addFieldToFilter() on a non-object" in admin page
I Create a new table with the name of "directory_country_city",then i config the new table in my config.xml its placed in "FederalLawyer/app/code/local/Federallawyer/Customer/config.xml"
SUPEE6788 and SQL statements in addFieldToFilter
At SUPEE-6788 Technical Details the Magento people give examples about how to fix Magento modules. Anyway, I have some places I found after install the SUPEE6788 patch that isn't explained in examples maybe someone could help me with. I have this: I'm just wondering how to fix it? Like this: ('main_table .order_id',array('in" =>$ordersIds)) i.e....

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.