Magento collection has two methods for filtering bellow different
- 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%')))
- 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:
- "and" (default) - adds AND $field=$value to the WHERE clause
- "or" - adds "OR $field=$value to the WHERE clause
See More Details