Use final price in addFieldToFilter

Question

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);

Answer 1

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));

Answer 2

The safest way to add price filters is using the existing methods that take advantage of the price index.

Example:

// join the price index
$collection->addFinalPrice();

// add filters
$collection->addPriceDataFieldFilter('%s <= %s', ['final_price', $minPrice]);
$collection->addPriceDataFieldFilter('%s >= %s', ['final_price', $maxPrice]);

Alternatively, look at the layered navigation to see how price filters are implemented there.