Magento 1.8 – Filter Product Collection by Multiple Price Ranges

collection-filteringmagento-1.8price

I want to filter product collection by some attributes including diy_kit_price attribute (new attribute already added) as shown below.

Price Ranges:

  1. 0 – 5000
  2. 10000 – 15000

I tried several ways but no luck.

Working Code: when i am filtering by one price range:

$products = Mage::getModel('catalog/product')->getCollection()
         ->addAttributeToSelect('*')
         ->addAttributeToFilter('type_id', 'configurable')
         ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
         ->addAttributeToFilter('category_id', array(
             array('finset' => '37')
           ))                
         ->addAttributeToSort('name', 'asc')
         ->addAttributeToFilter('diy_kit_price', array('gt' => 0))
         ->addAttributeToFilter('diy_kit_price', array('lt' => 5000));

Not Working Code: when i am filtering by one price range:

$products = Mage::getModel('catalog/product')->getCollection()
         ->addAttributeToSelect('*')
         ->addAttributeToFilter('type_id', 'configurable')
         ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
         ->addAttributeToFilter('category_id', array(
             array('finset' => '37')
           ))                
         ->addAttributeToSort('name', 'asc')
         ->addAttributeToFilter('diy_kit_price', array('gt' => 0))
         ->addAttributeToFilter('diy_kit_price', array('lt' => 5000))
         ->addAttributeToFilter('diy_kit_price', array('gt' => 10000))
         ->addAttributeToFilter('diy_kit_price', array('lt' => 15000));

Is there any other way to filter. Can we filter using plain SQL with multiple price ranges.

Thanks in advance.

Best Answer

Got solution... Hurreee...:-)

->addFieldToFilter('diy_kit_price', 
     array(array('from'=>'0','to'=>'5000'), 
     array('from'=>'15000','to'=>'20000'))
 )