addAttributeToFilter Conditionals In Magento

addAttributeToFilter is a function that can be called on a product collection in Magento. In short, it adds a condition to the WHERE part of the MySQL query used to extract a product collection from the database.

$_products = Mage::getModel('catalog/product')->getCollection()
   ->addAttributeToSelect(array('name', 'product_url', 'small_image'))
   ->addAttributeToFilter('sku', array('like' => 'UX%'))
    ->load();

The above code would get a product collection, with each product having it's name, url, price and small image loaded in it's data array. The product collection would be filtered and contain only products that have an SKU starting with UX.

addAttributeToFilter Conditionals

Notice above, I used the LIKE operator? There are many more operators in SQL and addAttributeToFilter will accept them all. I include them below as well as a reference for you. Hopefully this will save you some time.

Equals: eq

$_products->addAttributeToFilter('status', array('eq' => 1));

Not Equals - neq

$_products->addAttributeToFilter('sku', array('neq' => 'test-product'));

Like - like

$_products->addAttributeToFilter('sku', array('like' => 'UX%'));

One thing to note about like is that you can include SQL wildcard characters such as the percent sign.

Not Like - nlike

$_products->addAttributeToFilter('sku', array('nlike' => 'err-prod%'));

In - in

$_products->addAttributeToFilter('id', array('in' => array(1,4,98)));

When using in, the value parameter accepts an array of values.

Not In - nin

$_products->addAttributeToFilter('id', array('nin' => array(1,4,98)));

NULL - null

$_products->addAttributeToFilter('description', 'null');

Not NULL - notnull

$_products->addAttributeToFilter('description', 'notnull');

Greater Than - gt

$_products->addAttributeToFilter('id', array('gt' => 5));

Less Than - lt

$_products->addAttributeToFilter('id', array('lt' => 5));

Greater Than or Equals To- gteq

$_products->addAttributeToFilter('id', array('gteq' => 5));

Less Than or Equals To - lteq

$_products->addAttributeToFilter('id', array('lteq' => 5));

addFieldToFilter()

Debugging The SQL Query

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

// Method 1
Mage::getModel('catalog/product')->getCollection()->load(true);

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

echo $collection->getSelect();

Both method 1 and method 2 will print out the query but both will do it in slightly different ways. Method 1 prints the query out as well as loading the products while method 2 will just convert the query object to a string (ie. will print out the SQL). The second method is definitely better as it will be executed much quicker but I include them both here for reference.

Read more about the Magento database in the Direct SQL Queries in Magento article.

24 thoughts on “addAttributeToFilter Conditionals In Magento”

  • Reps

    Thanks, great post!

    Reply
  • Benham

    Really nice post and its nice to see you can use $collection->getSelect(); i always used to do $collection->printlogquery(true);

    Reply
  • BT

    $collection->getSelect() is great for viewing the query, but it also provides a way to extensively customise your SQL query!

    Try the following on a product collection (or any collection) in Magento.

    $collection = Mage::getModel(‘catalog/product’)->getCollection();
    echo ‘

    ';
    print_r(get_class_methods($collection->getSelect()));
    exit;

    This will list all of the available getSelect() functions and as you will be able to see, they are extremely useful!

    Reply
  • JEROME

    Thanx a lot great POST !!!!

    Reply
  • NickSun

    Magento 1.4.1.1
    NULL – null
    $_products->addAttributeToFilter('description', array('null'=>1));

    Not NULL – notnull
    $_products->addAttributeToFilter('description', array('notnull'=>1));

    see more at \lib\Varien\Data\Collection\Db.php

    Reply
  • diszo

    Nice post! :)

    Reply
  • Ela

    I want to show products based on my sorted list rather than default magento sort. So i added a attribute called product_display_order and will input the integer value while adding/updating depends on situations. So those things over but where and how can i add the condition to the product collection so that magento sorts the collection based on my attribute.

    Please help me

    Reply
  • Kiran

    Simple post but useful for beginners....

    Reply
  • roshan

    First of all thanks for this wonderful post. I have one question regarding collection or products that we get after filter.
    I am getting different collections using the custom attributes but pagination is not working for my custom collection. This is the url where I am using all the queries:http://weddingday.gemfindwebdesign.com/engagement-rings.html/

    I want to use pagination also when I choose custom collection. Any help or any hint? Thanks

    Reply
  • Gary Hussey

    Excellent Post, thank you very much for sharing this knowledge, you've saved me hours of frustration and delay (shout out for Sir Topham Hat).

    Reply
  • Thomas

    Very nice description!

    But can you tell me how to send this mysql command with the query: DATE(), because i want to only show the date part, without the time part, of certain attributes in the grid.
    this one: ->addAttributeToSelect('featured_from') gives: "2011-10-18 00:00:00", how do i adjust the query so it only returns "2011-10-18"?
    I'm thinking about something like ->addAttributeToSelect('featured_from', array('DATE')) ...

    Reply
  • mahesh

    Good post and i have one problem

    How can i list the products sort by image in Magento?

    Ex: which product have the image that should come first and which product don't have the image that should come last

    Reply
  • MagePsycho

    For NULL and NOT NULL, you need to use as:
    $collection->addFieldToFilter('field', array('null' => true)); // WHERE field IS NULL
    $collection->addFieldToFilter('field', array('notnull' => true)); // WHERE field IS NOT NULL

    Hope this helps.
    Cheers!

    Reply
  • Jakub Kramarz

    In reply to addFieldToFilter() vs addAtributeToFilter() problem:

    Function addFieldToFilter adds query condition to fields in specific table, on which you are running query. Unfortunatelty, products catalog uses EAV model, so there is no such field - it must be joined in special way to attributes table - it's why you must be using addAtributeToFilter.

    If specific collection is just instance of Mage_Core_Model_Mysql4_Collection_Abstract - you should be using addFieldToFilter. If it's Mage_Eav_Model_Entity_Collection_Abstract - addAtributeToFilter.

    Reply
  • Mark Robinson
    Mark Robinson March 1, 2012 at 1:25 pm

    Thanks for the post. Any suggestions how to do something like this:
    ->addAttributeToFilter('special_price/price', array('lteq' => '0.8'))

    So I want the products that have been discounted by more than 20%

    Reply
  • Debashis Chowdhury
    Debashis Chowdhury March 20, 2012 at 6:34 am

    Hi..

    I was roaming around for help and at came to this site. It helped me a lot on filter process.

    Thanks a lot for a nice article..

    Reply
  • Mitch

    You can also get your current filters and apply them to the product collection.


    $_filters = Mage::getSingleton('Mage_Catalog_Block_Layer_State')->getActiveFilters();

    foreach ($_filters as $filter) {
    $filterData = $filter->getData();

    //var_dump($filterData['filter']->getRequestVar());
    //var_dump($filterData['value']);

    if ($key && $val) {
    $product_collection->addAttributeToFilter($key, array('in' => $val));
    }
    }

    Reply
  • Roberto Alonso
    Roberto Alonso May 15, 2012 at 12:42 am

    Thanks for your post. Is there any way to add filter order by rating?

    thanks alot

    Reply
  • beaudame

    Good article,
    I have one question if u can help
    How I can add filter to find data between two price range? for example show all products greater than 10 and less than 20 ?

    Thanks

    Reply
  • aryan

    Hi beaudame,

    For this you have to add two filters something like below,


    ->addAttributeToFilter('price', array('gt' => $minprice))
    ->addAttributeToFilter('price', array('lt' => $maxprice))

    Reply
  • clockworkgeek

    Roberto Alonso,

    I have a way to filter by ratings here: http://www.magentocommerce.com/magento-connect/catalog/product/view/id/15706/
    It's not as simple as "addAttributeToFilter" because ratings are not direct attributes of products. (I'm sorry for kidnapping thread for my own purposes)

    Reply
  • rosh

    Great tutorial,
    A minor note filtering for NULL may require:
    $_products->addAttributeToFilter('description', array('null' => 1), 'left');
    (i.e add 'left' join as by default its 'inner' join which will not join to null attributes for an eav table as those rows would not exist on the attribute table)

    Reply
  • Pradeep K Maurya

    Hi,
    how will we solve this issue "Configurable Product turn Out of Stock when all child product are out of stock"? on magento website


    Thanks

    Reply
  • Leek

    I use filter : $_products->addAttributeToFilter('id', array('nin' => array(1,4,98)));
    But error : Fatal error: Call to a member function isStatic() on a non-object in E:\Xampp\htdocs\12tune\app\code\core\Mage\Eav\Model\Entity\Collection\Abstract.php on line 430

    Help me use it. Thanks!

    Reply
Leave a Reply
Post your comment

FishPig Ltd