There was an interesting question on StackExchange recently that did not get much attention:
Magento collection has two methods for filtering:
Seems that both methods add where condition to
Zend_Db_Select. And what advantages does
addFilterbring? When should I use it instead of
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
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
They methods have a different signature, where
addFilter seems to be less flexible, but you’ll see that it has its advantages as well:
/** * 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)
addFieldToFilter() can take an array of fields with an array of conditions, or a single field with a single condition:
addFieldToFilter(['field1', 'field2'], ['value1', 'value2']);
field1=value1 OR field2=value2
Each condition can be:
- a single scalar value
- an array in the form
[ operator => value ]
- 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' => $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
- if you need the date parsing feature but only to compare one of
>=, you can omit either ‘from’ or ‘to’.
'datetime' => trueis 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
datetimework the same way as
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', ));
/** * Add collection filter *s * @param string $field * @param string $value * @param string $type and|or|string */ public function addFilter($field, $value, $type = 'and')
addFilter() only allows filtering a single field by a single value and a type.
$type can be any of:
- “and” (default) – adds
AND $field=$valueto the WHERE clause (of course with proper quoting)
- “or” – adds
"OR $field=$valueto the WHERE clause (ditto)
- “string” – adds
AND $valueto 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.
Varien_Data_Collection_Db::_renderFilters() you can see how they are processed.
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.