Magento collection where (a AND b) OR (c and d) [duplicate]

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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