7

I have a collection $c. If I write

$c->addFieldToFilter('id',array(1,2,3,4))
  ->addFieldToFilter('sku',array('a','b','c','d'));

The resulting SQL WHERE-part will be:

 WHERE (((id = '1') OR (id = '2') OR (id = '3') OR (id = '4'))) AND (((sku = 'a') OR (sku = 'b') OR (sku = 'c') OR (sku = 'd')))

What I would need is

WHERE (id = '1' AND sku = 'a') OR (id ='2' AND sku = 'b') OR ...

Can I do this with Magento collections? How?

1
  • 3
    This question isn't a duplicate. The other answer is about OR inside AND, this is about AND inside OR. (note the AND and OR are switched in the condition) Commented Jun 12, 2017 at 21:08

3 Answers 3

8

According to the link provided by meenakshi, the right way is:

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

The where clause will result in:

WHERE title LIKE '%$titlesearchtext%' OR content LIKE '%$contentsearchtext%'

If you still have some doubts go to http://stackoverflow.com/questions/3826474/magento-addfieldtofilter-two-fields-match-as-or-not-and/7851884#7851884">here

5

You can do it as follows:

$c
  ->getSelect()
  ->where("id = '1' AND sku = 'a'")
  ->orWhere("id ='2' AND sku = 'b'")
   ...

Update: You can use built in zend framework SQL protection as follows:

$c
  ->getSelect()
  ->where("id = ? AND sku = ?", $var1, $var2)
  ->orWhere("id = ? AND sku = ?", $var3, $var4)
   ...

(according to the following SO answer https://stackoverflow.com/a/18889623/2115770)

New code is untested but should work.


I'm not sure why anybody would flag this answer for deletion, since it's the only correct answer given.

13
  • 1
    This works, but it kills a lot of magento logic which depends on collections. Beside this you are open for SQL-Injections if you make it wrong - and EVERYONE makes it wrong. Commented Nov 16, 2016 at 11:02
  • All other answers are incorrect, this is the only one that does what has been asked (nested AND inside OR). I agree regarding SQL vulnerability, so if anyone knows a better method they should add a new answer. Otherwise I'd of course recommend to sanitize user input if any. Commented Nov 17, 2016 at 20:46
  • 1
    Even if the question is showing the reality (which I doubt) the next one looking for an answer will write vulnerable code :-) Commented Nov 17, 2016 at 22:39
  • 1
    I did update the question with zend placeholder syntax by the way, which should prevent SQL injection Commented Nov 17, 2016 at 22:51
  • 1
    ->where("id = ? AND sku = ?", $var1, $var2) esults in both question marks taking $var1
    – snh_nl
    Commented Dec 4, 2018 at 14:32
2

You can use OR condition as following code

$collection->addFieldToFilter(array(
                array('attribute'=>'id', array('eq' => '1')),
                array('attribute'=>'sku',  array('eq' => 'a'))));

Refer this Link

3
  • I get ((Array = '') OR (Array = '')) Commented Jul 8, 2015 at 13:58
  • This is nice answered!
    – HungDQ
    Commented Jul 8, 2015 at 17:02
  • Doesn't work, @Rusit0 works. Commented Nov 16, 2016 at 11:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.