RSS

Magento addAttributeToFilter SQL Conditionals

15 Jul

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.

1
<?php $collection = Mage::getModel('catalog/product')->getCollection(); ?>

If you want get all fields then use below condition select

1
<?php $collection->addAttributeToSelect('*'); ?>

If you want to get limited fields then use below condition

1
<?php $collection->addAttributeToSelect(array('name', 'product_url', 'small_image')); ?>
1
2
3
4
5
6
<?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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?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.

1
2
3
4
5
6
7
8
<?php
// Method 1
Mage::getModel('catalog/product')->getCollection()->load(true);
 
// Method 2 (Quicker, Recommended)
$collection = Mage::getModel('catalog/product')->getCollection();
echo $collection->getSelect();
?>
Advertisements
 
Leave a comment

Posted by on July 15, 2013 in Magento

 

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: