Magento 1 Tutorials addAttributeToFilter Conditionals In Magento

First Published: April 16, 2010

addAttributeToFilter is a method that can be called on EAV collections in Magento 1 and Magento 2. This includes product collections, category collections, customer collections and many more. In short, it adds a condition to the WHERE part of the MySQL query used to extract a collection from the database, therefore allowing you to filter the collection by custom conditions. Mastering this method is key if you want to learn to write great code and great Magento extensions.

Improve your Magento SEO and add a WordPress blog with our free Magento WordPress Integration extension.

addFieldToFilter

addAttributeToFilter is a method that can be called on any EAV collection. For none-EAV collections, you can use addFieldToFilter. This method takes the same arguments as addAttributeToFilter so once you know how to use one, you will know how to use both.

addAttributeToFilter Conditionals

There are many operators in SQL and addAttributeToFilter will accept all of them, as long as you use the correct syntax. I've listed them all below and provided examples.

Equals: eq

This is the default operator and does not need to be specified. Below you can see how to use the operator, but also how to skip it and just enter the value you're using.

$_products->addAttributeToFilter('status', array('eq' => 1)); // Using the operator
$_products->addAttributeToFilter('status', 1); // Without using the operator

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, which matches any characters.

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', array('null' => true));

Not NULL - notnull

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

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

Debugging The SQL Query

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

/*
 * This is the better way to debug the collection
 */
$collection->load(true);

/*
 * This works but any extra SQL the collection object adds before loading
 * may not be included
 */
echo $collection->getSelect();

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

Convert your category URLs to shorter flatter URLs with the Flat Category URLs extension and see better category SEO.