Filters in Query in Magento

Hello All,

When we are filtering data in Magento, time occurs when we want to fetch result after filters like Not equal, greater than, less than, etc.

The addFieldToFilter method’s second parameter is used for this. 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.

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

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'

More can be found here Magento Dev.

Hope its helpful.