Magento 1.9 : How to apply more than one price range on product collection

Question

I want to apply multiple price ranges on final price of product collection. I have tried below code.

$collection->addFinalPrice();
$collection->getSelect()
->where('price_index.final_price <= ' . $maxprice)- 
>where('price_index.final_price >= ' . $minprice);

But from above method i can only apply one range to it. Also price range is dynamic. It can be one or more than one. I found following solution regarding my problem

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

But addFieldToFilter is not working on $collection. Any help will be appreciated.

Answer 1

You can pass more complex conditions to the where() method and there is also a orWhere method which appends the condition with OR.

For example this should do what you expect:

$collection->getSelect()
->where(diy_kit_price >= 0 AND diy_kit_price <= 5000) 
->orWhere(diy_kit_price >= 15000 AND diy_kit_price <= 20000);