Filter Product Collection By Another Attribute In Product Collection eg 'attribX', array('gt' => 'attrib-Y')

Question

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

Answer 1

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

Answer 2

You can use Zend_Db_Expr to use raw SQL instead of values. But with product collections, you have to be careful because the query is different if the flat product index is enabled

EAV (flat index turned off or admin area)

Here we need to add a filter for the second attribute as well because the attributes to be selected will be loaded in a second query and you have no access to it otherwise. Choose something that's always true, I went for 'notnull'. The joined attribute table can be referenced with "at_" + attribute code:

$_productCollection
    ->addAttributeToFilter('price', ['notnull' => true])
    ->addAttributeToFilter(
        'special_price',
        ['lt' => new Zend_Db_Expr('at_price.value')]
    );

Using flat index

With the flat index you can access any attribute directly that is included in the flat index (i.e. "Used in product listing"):

$_productCollection->addAttributeToFilter(
    'special_price',
    ['lt' => new Zend_Db_Expr('price')]
);

Universal solution

Using addExpressionAttributeToSelect() as suggested by @Marius, we can make sure that all attributes used in the expression are available, without having to add a dummy filter (both are just workarounds but work equally well). Then we only need to use a different expression, depending on isEnabledFlat():

$priceExpression = $_productCollection()->isEnabledFlat()
    ? new Zend_Db_Expr('price')
    : new Zend_Db_Expr('at_price.value');
$_productCollection
    ->addExpressionAttributeToSelect('difference', '(IF ({{special_price}} > {{price}} , 1, 0))', array('special_price', 'price'));
    ->addAttributeToFilter(
        'special_price',
        ['lt' => $priceExpression]
    );

Note that we don't actually use difference in the filter, because WHERE is executed before the value is calculated. But this also means, MySQL can optimize the query better than with HAVING, which is executed last.