Magento addAttributeToFilter SQL Conditionals

Home / Magento / Magento addAttributeToFilter SQL Conditionals

If you want to customize query or product collection data you can easily to do that with addAttributeToFilter funcation in magento. In short you can easily customize product collection select query based on your requirement in magento development.

<?php $collection = Mage::getModel('catalog/product')->getCollection(); ?>

If you want get all fields then use below condition select

<?php $collection->addAttributeToSelect('*'); ?>

If you want to get limited fields then use below condition

<?php $collection->addAttributeToSelect(array('name', 'product_url', 'small_image')); ?>
$collection = Mage::getModel('catalog/product')->getCollection()
   ->addAttributeToSelect(array('name', 'product_url', 'small_image'))
   ->addAttributeToFilter('sku', array('like' => 'test-product%'))

The above code would get a product collection which contain only products that have an SKU starting with “test-product”.

addAttributeToFilter Conditionals

// Is Equal To (eq)
$collection->addAttributeToFilter('status', array('eq' => 1));

// Is Not Equal To (neq)
$collection->addAttributeToFilter('sku', array('neq' => 'test-product'));

// Greater Than (gt)
$collection->addAttributeToFilter('price', array('gt' => 10.05));

// Less Than (lt)
$collection->addAttributeToFilter('price', array('lt' => 15.09));

// Greater Than or Equal To (gteq)
$collection->addAttributeToFilter('price', array('gteq' => 2.29));

// Less Than or Equal To (lteq)
$collection->addAttributeToFilter('price', array('lteq' => 5.44));

// Contains (like) - also uses % wildcards
$collection->addAttributeToFilter('sku', array('like' => 'test-product%'));

// Does Not Contain (nlike) - also uses % wildcards
$collection->addAttributeToFilter('sku', array('nlike' => 'test-product%'));

// In Array (in)
$collection->addAttributeToFilter('id', array('in' => array(1,3,12)));

// Not In Array (nin)
$collection->addAttributeToFilter('id', array('nin' => array(1,2,12)));

// Is NULL (null)
$collection->addAttributeToFilter('description', 'null');

// Is Not NULL (notnull)
$collection->addAttributeToFilter('description', 'notnull');

Debugging The SQL Query
There are two ways to debug the query being executed when loading a collection in Magento.

// Method 1
// Method 2 (Quicker, Recommended)
$collection = Mage::getModel('catalog/product')->getCollection();
echo $collection->getSelect();

Related Posts


  • Magento developers many times get confused for designing a website in a unique way. After visiting this article, one can easily get the overview of designing a particular Ecommerce website. As the matter of fact, various platforms require different kinds of designs for better engagement of a product. So after visiting here, one can easily get the best overview of designing a website.

  • Heya! I just wanted to ask if you ever have any problems with hackers?
    My last blog (wordpress) was hacked and I ended up losing many months of hard
    work due to no data backup. Do you have any solutions to
    stop hackers?

  • Hello! I know this is kinda off topic but I was wondering which blog platform are you using
    for this website? I’m getting fed up of WordPress because I’ve had issues with hackers and I’m looking at options for another platform.
    I would be awesome if you could point me in the direction of a good platform.

Leave a Reply

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