Magento addFieldToFilter: Two fields, match as OR, not AND

Question

Hi All,

I've been stuck on this for the last few hours. I got it working by hacking a few lines in /lib/Varien/Data/Collection/Db.php, but I'd rather use the proper solution and leave my core untouched.

All I need to do is get a collection and filter it by two or more fields. Say, customer_firstname and remote_ip. Here's my (disfunctional without hacking Db.php) code:

$collection = Mage::getModel('sales/order')->getCollection()->
addAttributeToSelect("*")->
addFieldToFilter(array(array('remote_ip', array('eq'=>'127.0.0.1')),
array('customer_firstname', array('eq'=>'gabe'))), array('eq'=>array(1,2,3)));

With a stock Db.php, I tried this: (sample taken from http://magentoexpert.blogspot.com/2009/12/retrieve-products-with-specific.html)

$collection->addFieldToFilter(array(
    array('name'=>'orig_price','eq'=>'Widget A'),
    array('name'=>'orig_price','eq'=>'Widget B'),           
));

But that gives me this error:

Warning: Illegal offset type in isset or empty  in magento/lib/Varien/Data/Collection/Db.php on line 369

If I wrap that with a try/catch, then it moves into _getConditionSql() and gives this error:

Warning: Invalid argument supplied for foreach()  in magento/lib/Varien/Data/Collection/Db.php on line 412

Does anyone have any working, functional code for doing this? I'm running Magento 1.9 (Enterprise). Thanks!

Answer 1

To filter by multiple attributes use something like:

//for AND
    $collection = Mage::getModel('sales/order')->getCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter('my_field1', 'my_value1')
    ->addFieldToFilter('my_field2', 'my_value2');

    echo $collection->getSelect()->__toString();

//for OR - please note 'attribute' is the key name and must remain the same, only replace //the value (my_field1, my_field2) with your attribute name


    $collection = Mage::getModel('sales/order')->getCollection()
        ->addAttributeToSelect('*')->addFieldToFilter(array(array('attribute'=>'my_field1','eq'=>'my_value1'),array('attribute'=>'my_field2', 'eq'=>'my_value2')));

For more information check: http://docs.magentocommerce.com/Varien/Varien_Data/Varien_Data_Collection_Db.html#_getConditionSql