In general MySQL uses where clause to extract only those record which fulfill a specific criteria.In same way we can use where clause in magento to filter our data for certain criteria for collection of data.For that magento uses the method addAttributeToFilter and addFieldToFilter for collecting data with certain criteria.Like greater than ,equal, not equal etc.So as I here told you two methods for collecting data those are described below.Use the trick to get collection with where clause in magento for product collection as well as custom collection for custom module.
You may also the Magento2 article: where clause in magento2
1) addAttributeToFilter() :
addAttributeToFilter is a function that can be called on a product collection in Magento which add the conditions to where part of mysql query to extract product collection.
Suppose I have an object $_product from catalog model like
$_products = Mage::getModel(‘catalog/product’)
.For for setting certain condition to my collection I will add the function addAttributeToFilter() like way:
$_products = Mage::getModel('catalog/product')->getCollection() ->addAttributeToSelect(array('name', 'product_url', 'small_image')) ->addAttributeToFilter('sku', array('eq' => 'nokia123')) ->load();
If you like to see what will be the custom sql query for above code then its simple you just print or log $_product object then see what magic did you get now.That is
Either use this :
print_r((string)$_products->getSelect());exit;
or
Mage::log((string)$_products->getSelect(),1,’MyQuery.log’);
.Then you will see below sql query in you screen or in log(log must be enable in back-end) file as per your printing code.
SELECT `e`.* FROM `catalog_product_entity` AS `e` WHERE (`e`.`sku` = ‘nokia123’)
2)addFieldToFilter():
addFieldToFilter() generally can be used if you are using your custom module and want to retrieve data with where clause in magento.Its similar to addAttributeToFilter() method but why they are different because as your module has only simple column not any attribute.If your collection is an EAV type(table is Entity Event Type) then it is fine to use addAttributeToFilter.If you have table in Flat type then its better to use addFieldToFilter().Suppose I have a module called JRB_Customuser having model name jrbcustomuser Now here I would like fetch data from the table “jrb_customuser_jrbcustomuser” (which is Flat table)in my custom module so then my collection code will be like below.
$jrbcustomuser = Mage::getModel('jrb_customuser/jrbcustomuser')->getCollection() ->addFieldToSelect('*') ->addFieldToFilter('entity_id', array('eq' => '1')) ->load();
Either use this :
print_r((string)$jrbcustomuser->getSelect());exit;
or
Mage::log((string)$jrbcustomuser->getSelect(),1,’MyQuery.log’);
.Then you will see below sql query in you screen or in log(log must be enable in back-end) file as per your printing code.
SELECT `main_table`.* FROM `jrb_customuser_jrbcustomuser` AS `main_table` WHERE (entity_id = ‘1’)
So now I guess you got to know some idea how addAttributeToFilter is similar to where clause in magento.
Below some operators which are used in where clause for magento:
Like – like
$_products->addAttributeToFilter('sku', array('like' => 'Nok%'));
Not Like – nlike
$_products->addAttributeToFilter('sku', array('nlike' => 'err-prod%'));
Equals: eq
$_products->addAttributeToFilter('status', array('eq' => 1));
Not Equals – neq
$_products->addAttributeToFilter('sku', array('neq' => 'Nokia'));
In – in
$_products->addAttributeToFilter('id', array('in' => array(116,114,112)));
Not In – nin
$_products->addAttributeToFilter('id', array('nin' => array(116,114,112)));
NULL – null
$_products->addAttributeToFilter('description', 'null');
Not NULL – notnull
$_products->addAttributeToFilter('description', 'notnull');
Greater Than – gt
$_products->addAttributeToFilter('id', array('gt' => 50));
Greater Than or Equals To- gteq
$_products->addAttributeToFilter('id', array('gteq' => 50));
Less Than – lt
$_products->addAttributeToFilter('id', array('lt' => 100));
Less Than or Equals To – lteq
$_products->addAttributeToFilter('id', array('lteq' => 100));
Pingback:where clause in magento2 - Jyotiranjan