What’s the difference between addAttributeToSelect(), addAttributeToFilter() and addFieldToFilter() methods? As you know that a product can have many attributes. The addAttributeToSelect() method is used to select the attributes that we want to retrieve. So this method doesn’t filter out the collections, only the attributes. If we use addAttributeToSelect(‘*’), then all the attributes will be loaded.
Whilst addAttributeToSelect() is used to filter the attributes, the addAttributeToFilter() method is used to filter the collection based on the specified conditional parameters. addFieldToFilter() is also used to filter the collection but for non-EAV model. Entities like product, customer, sales, etc are using EAV-based models, thus theoritically it’s better to use addAttributeToFilter() for those entities. However, in Varien_Data_Collection_Db, addFieldToFilter() is mapped to addAttributeToFilter(). So, basically it’s also safe to use addFieldToFilter() in all EAV-based models.
$collections = Mage::getModel('catalog/product') |
|
->getCollection() |
|
->addAttributeToSelect(array('name', 'price')) |
|
->addAttributeToFilter('price', array('eq' => 100.00)) |
|
->load(); |
From the code above, in this case is a product collection, we call addAttributeToSelect() to select only “name” and “price” attributes for all products. Then we call addAttributeToFilter() to filter so only products with the price of 100.00 will be selected from the database. Again, even though catalog/product is using EAV model, it’s ok to use addFieldToFilter() if you want to.
If you notice we use “eq” (equal) as the condition to filter out the collection. There are many other conditions that we can use when filtering out the collection. Below is the complete list of all conditions that you can use when filtering out your collections.
Attribute Code | SQL Equivalent | Example |
eq | = | $collections->addAttributeToFilter(‘price’,array(‘eq’ => 10.00)); |
neq | != | $collections->addAttributeToFilter(‘price’,array(‘neq’ => 100.00)); |
like | LIKE | $collections->addAttributeToFilter(‘name’,array(‘like’ => ‘%fieldval%’)); |
nlike | NOT LIKE | $collections->addAttributeToFilter(‘name’,array(‘nlike’ => ‘%fieldval%’)); |
in | IN () | $collections->addAttributeToFilter(‘id’,array(‘in’ => array(1,2,3,4))); |
nin | NOT IN () | $collections->addAttributeToFilter(‘id’,array(‘nin’ => array(1,2,3,4))); |
is | IS | |
notnull | IS NOT NULL | $collections->addAttributeToFilter(‘description’, ‘notnull’); |
null | IS NULL | $collections->addAttributeToFilter(‘description’, ‘null’); |
moreq | >= | $collections->addAttributeToFilter(‘price’,array(‘moreq’ => 100.00)); |
gt | > | $collections->addAttributeToFilter(‘price’,array(‘gt’ => 100.00)); |
lt | < | $collections->addAttributeToFilter(‘price’,array(‘lt’ => 100.00)); |
gteq | >= | $collections->addAttributeToFilter(‘price’,array(‘gteq’ => 100.00)); |
lteq | <= | $collections->addAttributeToFilter(‘price’,array(‘lteq’ => 100.00)); |
finset | FIND_IN_SET() | $collections->addAttributeToFilter(‘custom’,array(‘finset’ => ‘1’)); |
from | >= | $collection->addAttributeToFilter(‘created_at’,array( ‘from’ => ’03 July 2014′, ‘to’ => ’10 March 2015′, ‘date’ => true )); |
to | <= | |
date | ||
datetime | $collection->addAttributeToFilter(‘created_at’, array( ‘from’ => ‘2015-01-01 00:00:00’, ‘to’ => ‘2015-12-31 00:00:00’, ‘datetime’ => true )); |
AND & OR Operation
When you do query in database, we often need to make either OR or AND operation or even both. How do we construct this in Magento?
$collections = Mage::getModel('catalog/product') |
|
->getCollection() |
|
->addAttributeToSelect(array('name', 'price')) |
|
->addAttributeToFilter('name', array('eq' => 'fieldval')) |
|
->addAttributeToFilter('price', array('eq' => 100.00)) |
|
->load(); |
When we make two or more calls on addAttributeToFilter() or addFieldToFilter() methods, we are doing an AND operation. From the code above, only product(s) that has a name of fieldval and price of 100.00 will be loaded.
$collections = Mage::getModel('catalog/product') |
|
->getCollection() |
|
->addAttributeToSelect( |
|
array('name', 'price') |
|
)
|
|
->addAttributeToFilter( |
|
array( |
|
'attribute' => 'name', |
|
'eq' => 'fieldval', |
|
),
|
|
array( |
|
'attribute' => 'price', |
|
'eq' => 100.00, |
|
),
|
|
)
|
|
->load(); |
To make an OR operation, we have to call the method once then put all the specified parameters in an array. From the code above, product(s) that has either a name of fieldval or price of 100.00 or both will be loaded.