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!

Solution 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%')
    )
)

Solution 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.

Solution 3

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

Solution 4

Here is my solution in Enterprise 1.11 (should work in CE 1.6):

    $collection->addFieldToFilter('max_item_count',
                    array(
                        array('gteq' => 10),
                        array('null' => true),
                    )
            )
            ->addFieldToFilter('max_item_price',
                    array(
                        array('gteq' => 9.99),
                        array('null' => true),
                    )
            )
            ->addFieldToFilter('max_item_weight',
                    array(
                        array('gteq' => 1.5),
                        array('null' => true),
                    )
            );

Which results in this SQL:

    SELECT `main_table`.*
    FROM `shipping_method_entity` AS `main_table`
    WHERE (((max_item_count >= 10) OR (max_item_count IS NULL)))
      AND (((max_item_price >= 9.99) OR (max_item_price IS NULL)))
      AND (((max_item_weight >= 1.5) OR (max_item_weight IS NULL)))

Solution 5

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

View more solutions

Comments

[Gabriel H]

Thanks, Anda. But that's for requiring both fields, not one or the other. If you add ->getSelectSql() to the end of that query, it gives you "...WHERE (my_field1 = 'my_value1') AND (my_field2 = 'my_value2')". It's the "WHERE (myfield1 = 'myvalue1') OR (myfield2 = 'myvalue2')" that I'm trying to get.

[leek]

+1 for this. @Anda's solution is not correct for an OR condition.

[Mageician]

I tried all the answers and this is the only answer that has worked for me. Anda's answer for the OR was trying to search for an attribute called 'attribute', so I kept getting unknown column 'attribute' errors. Other solutions had the right column names but no values. This gave me the correct results in the SQL: ... WHERE (('first_attribute' = 'some value') OR ('second_attribute' = 'some value'))

[rterrani]

This only works with addAttributeToFilter in EAV collections. The Riyazkhan answer is the correct.

[zuzuleinen]

Trying this way I get an Column not found: 1054 Unknown column 'Array' in 'where clause'error

[zuzuleinen]

This is also the best solution I know

[billmalarky]

I'd been trying to get a complicated query working for custom resource models for a few hours now. This is what finally worked. Thanks.

[Ariel Allon]

The reason this works is that it uses the keys in the first param (the array of fields) to lookup the value in the second param (the array of conditions) to use for that field. I find it a little clearer to do something like: $collection->addFieldToFilter( array('field_1'=>'field_1', 'field_2'=>'field_2'), array( 'field_1'=>array( array('in' => array('text_1', 'text_2', 'text_3'), array('like' => '%text') ), 'field_2'=>array('eq' => 'exact'), ) );

[Corgalore]

This filter actually worked for OR conditions. Thanks!

[user3338098]

note that this syntax is not correct for addAttributeToFilter, refer to stackoverflow.com/a/5301457/3338098 for that syntax

[user3338098]

note that this syntax is not correct for addAttributeToFilter, refer to stackoverflow.com/a/5301457/3338098 for that syntax