Magento – Use final price in addFieldToFilter

collection-filteringcollection;magento-1.9product-collectionspecial-price

I am using this code to filter products by min and max price

updated code

     $collection = Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect('*')
            ->addAttributeToFilter('status', 1)
            ->addAttributeToFilter(array(array('attribute'=>'visibility', 'eq'=>"4")));
     }
        if(sizeof($years)>0){
            $collection->addAttributeToFilter('year', array('in' => $years));
        }
        if(!empty($minprice) && !empty($maxprice)){
                $collection->addFieldToFilter('price',array('gteq'=>$minprice))
                           ->addFieldToFilter('price',array('lteq'=>$maxprice));
        }

But if products have special price it works wrong. I need to use final price here. How can I do this?

Answer that I found while exploring magento native layered navigation

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

Best Answer

Well the field for "final price" has code final_price in Magento Eav product table.

First you have to add final price to the collection as below:

$collection = Mage::getResourceModel('catalog/product_collection')->addAttributeToSelect('*');
$collection->addFinalPrice();

Then you can filter the collection by final_price as below:

$collection->addFieldToFilter('final_price',array('gteq'=>$minprice))
           ->addFieldToFilter('final_price',array('lteq'=>$maxprice));
Related Topic