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.

3 thoughts on “Filters in Query in Magento

  1. Hi P,

    I need to filter a numtiselect attribute.
    Filter is like musti_select_attribute != 126
    I am trying like (SQL terms)
    NOT FIND_IN_SET(126,musti_select_attribute)

    above query is working fine with SQL but I don’t know how to filter collection with addAttributeToFilter

    Please help me.

    1. I am not getting the query in whole, but Magento has this

      array("finset"=>array('n2610'))
      WHERE (find_in_set('n2610',e.sku))
      
      
       $allowedSchools = array(
        array(
          "finset" => array(348)
        ),
        array(
          "finset" => array(350)
        ),
      );
      $this->_productCollection->addAttributeToFilter("stv_school", $allowedSchools); 
      

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top