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')); ?>

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

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

addAttributeToFilter Conditionals

<?php
// 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.

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