How to compare two fields in a Magento query?

Question

I get all my active special products using this code I found somewhere:

$collection = $this->_addProductAttributesAndPrices($collection) ->addStoreFilter() ->addAttributeToFilter('special_from_date', array('date' => true, 'to' => $todayDate)) ->addAttributeToFilter('special_to_date', array('or'=> array( 0 => array('date' => true, 'from' => $todayDate), 1 => array('is' => new Zend_Db_Expr('null'))) ), 'left') ->setPageSize($this->get_prod_count()) ->setCurPage($this->get_cur_page()); 

Now I want to get only those products that have a special price <= price, but I still can’t figure out how to do it.

I read this page: http://www.magentocommerce.com/wiki/5_-_modules_and_development/catalog/using_collections_in_magento

and I tried this without success:

  ->addAttributeToFilter('special_price', array('lt' => 'price')) 

Thank you for your help!

Answer 1

You can try, thanks for Zyava!

In my case:

-> addAttributeToFilter ('price', array ('gt' => new Zend_Db_Expr ('final_price')))

Answer 2

A less efficient solution would be to iterate through the collection,

 $products = array(); $collection = Mage::getResourceModel('catalog/product_collection'); foreach($collection as $col) { $product = Mage::getModel('catalog/product')->load($col->getId()); if( $product->getPrice() > $product->getSpecialPrice() ) { $products[] = $product; } } 

you will have an array of products at the end ...