How we can add each type of attribute filters in magento

By | April 20, 2017
Spread the love

Sometimes we need to add custom condition on product listing or some custom pages to fetch data. Here you will how easily we can add conditions with addAttributeToFilter .  addAttributeToFilter is a method that can be called on EAV collections in Magento. 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 function is key if you want to learn to write great code and great Magento extensions.

This article will focus on product collections in Magento but all of the addAttributeToFilter calls can be used on any EAV collection in Magento.

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

addAttributeToFilter Conditionals In Magento

addAttributeToFilter is a method that can be called on EAV collections in Magento. 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 function is key if you want to learn to write great code and great Magento extensions.

This article will focus on product collections in Magento but all of the addAttributeToFilter calls can be used on any EAV collection in Magento.

$_products = Mage::getResourceModel('catalog/product_collection')
 ->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 that 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

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.

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

// Method 1
Mage::getResourceModel('catalog/product_collection')->load(true);

// Method 2
$collection = Mage::getResourceModel('catalog/product_collection')

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.

Leave a Reply

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

four × five =