addAttributeToFilter Conditionals In Magento

Posted on April 16, 2010 by BT There have been 11 comment(s)

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,74,98)));

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

Not In - nin

$_products->addAttributeToFilter('id', array('nin' => array(1,4,74,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()

As far as I'm aware, addAttributeToFilter only works with products in Magento. When I first found out this fact I was not only shocked, I was worried! I thought that without it, I would have to custom craft all of my SQL queries. After scouring the Magento core code one night, I found addFieldToFilter(). This functions works in the exact same way and takes the same paramters, however it works on ALL collections and not just on products!

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.

On a side note, I will soon be writing an article on the getSelect() function as it opens up a door in Magento Collections that gives them (and you) true power!


This post was posted in Magento Tutorials and was tagged with Magento Collections, Magento Tips

Similar Articles

If you're not finished learning, check out these related Magento tutorials.

  • Load A Category or Product by an Attribute

    Everyone knows how to load a product by an ID, but this isn't always enough to access the product you're after. Fortunately, Magento provides several...

  • Create a Drop-Down of Countries

    There are many ways to access country information in Magento. Unfortunately, most developers use a long drawn out way when they can accomplish it in...

11 Responses to addAttributeToFilter Conditionals In Magento

  • Reps
    Reps says:

    Thanks, great post!

    Posted on April 23, 2010 at 9:52 am

  • Benham
    Benham says:

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

    Posted on June 29, 2010 at 6:35 pm

  • fishpig
    fishpig says:

    $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!

    Posted on June 29, 2010 at 6:36 pm

  • JEROME
    JEROME says:

    Thanx a lot great POST !!!!

    Posted on September 1, 2010 at 11:13 am

  • NickSun
    NickSun says:

    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

    Posted on November 9, 2010 at 3:48 pm

  • diszo
    diszo says:

    Nice post! :)

    Posted on February 15, 2011 at 4:20 am

  • Ela
    Ela says:

    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

    Posted on February 15, 2011 at 4:58 am

  • Kiran
    Kiran says:

    Simple post but useful for beginners....

    Posted on August 31, 2011 at 2:43 pm

  • roshan
    roshan says:

    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

    Posted on September 19, 2011 at 7:33 pm

  • Gary Hussey
    Gary Hussey says:

    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).

    Posted on October 6, 2011 at 4:55 pm

  • Thomas
    Thomas says:

    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')) ...

    Posted on October 19, 2011 at 7:38 am

  • mahesh
    mahesh says:

    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

    Posted on November 5, 2011 at 7:23 am

  • MagePsycho
    MagePsycho says:

    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!

    Posted on January 5, 2012 at 12:30 pm

  • Jakub Kramarz
    Jakub Kramarz says:

    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.

    Posted on January 19, 2012 at 7:56 pm

  • Mark Robinson
    Mark Robinson says:

    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%

    Posted on March 1, 2012 at 1:25 pm

  • Debashis Chowdhury
    Debashis Chowdhury says:

    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..

    Posted on March 20, 2012 at 6:34 am

  • Mitch
    Mitch says:

    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));
    }
    }

    Posted on March 20, 2012 at 2:50 pm

17 Item(s)

Have your say...

You must be logged in to post a comment.

Magento Training

Our Magento tutorials are just a taster of what we can teach you. If you want to learn more, give us a call on 0845 544 1984 to arrange some on-site Magento development training.

Recent Posts
Recent Comments
© 2012 FishPig Limited