Josh Pennington Josh Pennington - 1 year ago 43
PHP Question

How do I compare 2 different fields in Magento in a collection

I am attempting to compare products price to its sale price in a Magento collection. Specifically, trying to find all the products who have a sale price that is greater than or equal to its base price.

Logically, I tried to just put the field names into the


->addAttributeToFilter('special_price', array('gteq' => 'special_price'));

But that did not work. Is there a good way to do this that will not require me to get every single product with a sale price to manually check if the sale price is greater than or equal to its base price?


This was quite strange and shows how odd Magento collections can be sometimes. It turns out that just because you put
into your call chain, does not mean you get price in a nice neat way. So what I had to do was put in a conditional so that I could get access to it in other parts of the query. I opted for
->addAttributeToFilter('price', array('gteq' => 0))
. I had to do the same for special price.

Then when it came to putting in the where condition, I still did not have access to the price and special price with easy names so I had to use their table values so my where statement ended up being
$products->getSelect()->where('_table_special_price.value >= _table_price.value');

In the end this is what my entire chain looked like (there are a few custom attributes in there):

$products = Mage::getModel('catalog/product')
->addAttributeToFilter('price', array('gteq' => 0))
->addAttributeToFilter('special_price', array('gteq' => 0))
->addAttributeToFilter('visibility', 4)
->addAttributeToFilter('discontinued', array('neq' => 1))
->addAttributeToFilter('status', 1)


Answer Source

Try this:

->addAttributeToFilter('special_price', array ('gteq' => new Zend_Db_Expr('price') ) );

If it doesn't work, you can go for this:

->getSelect()->where("special_price >= price");