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

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!

Solutions

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.

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

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

I also tried to get the field1 = 'a' OR field2 = 'b'

Your code didn't work for me.

Here is my solution

$results = Mage::getModel('xyz/abc')->getCollection();
$results->addFieldToSelect('name');
$results->addFieldToSelect('keywords');
$results->addOrder('name','ASC');
$results->setPageSize(5);

$results->getSelect()->where("keywords like '%foo%' or additional_keywords  like '%bar%'");

$results->load();

echo json_encode($results->toArray());

It gives me

SELECT name, keywords FROM abc WHERE keywords like '%foo%' OR additional_keywords like '%bar%'.

It is maybe not the "magento's way" but I was stuck 5 hours on that.

Hope it will help

Tags: PHP / Magento / Magento 1.9

Similar questions

I created two dynamic fields in admin but one of them not working in Magento 2?
Here I set two dynamic fields in one configuration but second dynamic field does not work My system.xml file
Magento Collection addFieldToFilter complicated AND/OR query
We have an events module on a Magento site we are developing which features event records which have start_date and end_date attributes. On the events listing view you can view events by month. Each month view should show events which 1) start in that month, 2) end in that month or 3) start before AND end after that month (ie. last for the duration...
magento customer module add new table and config show error "Fatal error: Call to a member function addFieldToFilter() on a non-object" in admin page
I Create a new table with the name of "directory_country_city",then i config the new table in my config.xml its placed in "FederalLawyer/app/code/local/Federallawyer/Customer/config.xml"
Difference between addAttributeToFilter and addFieldToFilter in Magento?
What's the difference between addAttributeToFilter and addFieldToFilter in Magento? I created a custom attribute YES/NO. But neither of them is working for me when I tried to pull a product collection. Its not filtering anything.
Magento2 - Destination fields are not mapped. Document: paypal_settlement_report_row. Fields: store_id
I tried to do migration from Magento1.9.3.8 to Magento2.3.4, when I ran the Magento Data migration tool I always got this error: Destination fields are not mapped. Document: paypal_settlement_report_row. Fields: store_id I tried to fix it by modifying the map_file in etc/opensource-to-opensource/1.9.3.8/map.xml.dist, but no luck
AddFieldToFilter And inside OR condition
I want to achieve the following query in magento custom model. How to achieve this in magento with AddFieldToFilter ? Not AddAttributeToFilter.

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.