How to compare two fields in a Magento query?

Question

I'm getting all my active special products using this code that I've 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 the products that have a special price <= price, however I still can't realize how to do it.

I've been reading 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'))

Thanks for your help!

That's right, that's why I'm asking, I don't know the right way to compare those values — Shaz, Nov 08 '11 at 17:55

Katapofatico · Accepted Answer · 2021-12-30T23:36:38.170

8

You can try this, thanks for Zyava!

On my case:

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

edited Dec 30 '21 at 23:36

answered Feb 17 '12 at 20:50

Katapofatico

750
1
10
29

score 0 · Answer 2 · answered Nov 08 '11 at 18:04

A lesser 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 in the end...

How to compare two fields in a Magento query?

2 Answers2