Doctrine DQL WHERE (condition1 and condition2) OR (condition3 and condition4)

Question

I am trying to do something similar to this SQL in DQL:

SELECT * FROM TBL
WHERE (something = foo AND something2 = foo2) 
OR (something3 = foo3 AND something4 = foo4)

I got this that seems to work but I am seriously doubting it; what do you think ?

->where('something = :foo')
->setParameter('foo', $fooValue)
->andWhere('something2 = :foo2')
->setParameter('foo2', $fooValue2)

->orWhere('something3 = :foo3')
->setParameter('foo3', $fooValue3)
->andWhere('something4 = :foo4')
->setParameter('foo4', $fooValue4)

->getQuery()
->getArrayResult()

score 0 · Answer 1 · answered Sep 04 '18 at 15:15

0

You can use $em->createQuery method

$query = $em->createQuery('SELECT t FROM Tbl t WHERE (t.something = :foo AND t.something2 = :foo2) OR (t.something3 = :foo3 AND t.something4 = :foo4)');
$query->setParameter(...);
$result = $query->getResult();

answered Sep 04 '18 at 15:15

Denis Alimov

2,861
1
18
38

score 0 · Accepted Answer · answered Sep 12 '18 at 05:59

Your doubt is correct and you may face operator prcedence issue with the current code you have, To write equivalent query with the grouping conditions in doctrine you can write as follows

->where(
    $qb->expr()->orX(
            $qb->expr()->andX(
                $qb->expr()->eq('something', ':foo'),
                $qb->expr()->eq('something2', ':foo2')
            ), 
            $qb->expr()->andX(
                $qb->expr()->eq('something3', ':foo3'),
                $qb->expr()->eq('something4', ':foo4')
            )
        )
)
->setParameter('foo', $fooValue)
->setParameter('foo2', $fooValue2)
->setParameter('foo3', $fooValue3)
->setParameter('foo4', $fooValue4)
->getQuery()
->getArrayResult();

Doctrine Query Builder nested orX and andX conditions with join

Doctrine DQL WHERE (condition1 and condition2) OR (condition3 and condition4)

2 Answers2