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

Question

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

I've got another way to add an or condition in the field:

->addFieldToFilter(     array('title', 'content'),     array(         array('like'=>'%$titlesearchtext%'),          array('like'=>'%$contentsearchtext%')     ) ) 

Answer 2

OR conditions can be generated like this:

$collection->addFieldToFilter(     array('field_1', 'field_2', 'field_3'), // columns     array( // conditions         array( // conditions for field_1             array('in' => array('text_1', 'text_2', 'text_3')),             array('like' => '%text')         ),         array('eq' => 'exact'), // condition for field 2         array('in' => array('val_1', 'val_2')) // condition for field 3     ) ); 

This will generate an SQL WHERE condition something like:

... WHERE (          (field_1 IN ('text_1', 'text_2', 'text_3') OR field_1 LIKE '%text')       OR (field_2 = 'exact')       OR (field_3 IN ('val_1', 'val_2'))     ) 

Each nested array(<condition>) generates another set of parentheses for an OR condition.