Posted on by Fabian Schmengler

Filtering Magento Collections: addFilter() vs. addFieldToFilter()

There was an interesting question on StackExchange recently that did not get much attention:

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 ?

I had to dig a bit into addFilter myself because I was only familiar with addFieldToFilter and here is what I found:

Link to the question: addFilter vs addFieldToFilter

Full answer

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:

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
  • 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’.

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' =&gt; array(
    'price'         =&gt; 'price_index.price',
    'final_price'   =&gt; 'price_index.final_price',
    'min_price'     =&gt; 'price_index.min_price',
    'max_price'     =&gt; 'price_index.max_price',
    'tier_price'    =&gt; 'price_index.tier_price',
    'special_price' =&gt; 'price_index.special_price',
));

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.

Related Posts: