Magento – Filter Product Collection By Another Attribute In Product Collection eg ‘attribX’, array(‘gt’ => ‘attrib-Y’)

collection-filteringcollection;filterproduct-collection

When using product collections like so:

$_productCollection= Mage::getModel('catalog/product')->getCollection()
                    ->addAttributeToSelect('*')
                    ->addAttributeToFilter('special_price', array('neq'=>''));

Is it possible to use addAttributeToFilter on an attribute relative to another attribute? e.g. Can I filter special_price greater than price with something like

$_productCollection= Mage::getModel('catalog/product')->getCollection()
                    ->addAttributeToSelect('*')
                    ->addAttributeToFilter('special_price', array('gt'=>'price'));

Best Answer

I think you can achieve this using addExpressionAttributeToSelect. See class Mage_Eav_Model_Entity_Collection_Abstract for this. You can try something like this:

$_productCollection= Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->addExpressionAttributeToSelect('difference', '(IF ({{special_price}} > {{price}} , 1, 0))', array('special_price', 'price'));

This will add a new "column" named difference to the select that has the value 1 if the special price is bigger than the price. Now all you have to do is filter by that column.

$_productCollection->getSelect()->having('difference = ?', 1);
Related Topic