How to use andWhere and orWhere in Doctrine?

Question

WHERE a = 1 AND (b = 1 Or b = 2) AND (c = 1 OR c = 2)

How can i make this in Doctrine?

$q->where("a = 1");
$q->andWhere("b = 1")
$q->orWhere("b = 2")
$q->andWhere("c = 1")
$q->orWhere("d = 2")

this isnt correctly... Should be:

$q->where("a = 1");
$q->andWhere("b = 1")
   $q->orWhere("b = 2")
$q->andWhere("c = 1")
   $q->orWhere("d = 2")

but how can i make it? In Propel is function getNewCriterion, and in Doctrine...?

Maerlyn · Accepted Answer · 2013-06-07T08:26:13.457

131

$q->where("a = 1")
  ->andWhere("b = 1 OR b = 2")
  ->andWhere("c = 2 OR c = 2")
  ;

edited Jun 07 '13 at 08:26

answered Feb 01 '12 at 11:44

Maerlyn

33,687
18
94
85

17

Then why not just put it all in the `where()` call? – MatBailie Feb 01 '12 at 11:48
3

I wouldn't be satisfied with this kind of syntax... What if you try to migrate to some sick kind of "SQL" where `OR` will be replaced by `||`... How does this work, when you need to use `b = ? OR b = ?` – Vyktor Feb 01 '12 at 11:49
@Dems Yes, it is certainly possible. This method is faster, tho. – Maerlyn Feb 01 '12 at 11:50
5

@Vyktor: doctrine handles that. This is actually DQL, not SQL - Doctrine QL that is transformed to valid vendor-specific SQL transparent to you. Also: `andWhere("b=? OR b=?", array(1, 2))`. – Maerlyn Feb 01 '12 at 11:51
1

I think this should be $q->where("a = 1") ->andWhere("(b = 1 OR b = 2)") ->andWhere("(c = 2 OR c = 2)"); – Calin Blaga Feb 20 '17 at 08:34
It works but personally I don't consider this the correct approach. One of the main points of using a builder is to let the code describe your logic, not your strings (even if doctrine takes care of translating to the right language as Maerlyn said). Another main point of using a builder is to be able to compose your query without concatenating strings (for example what if you have a list of matches like [status = 'inProgress' OR status='done' OR status='whatever' etc.]). You want to be able to loop through a list when needed. I think Serge answer should be the accepted one. – zozo Jul 11 '19 at 10:11
Serge's answer to use the Expression builder is a more satisfying way to accomplish this task. – tlorens Sep 06 '19 at 03:14

Serge Kvashnin · Answer 2 · 2016-05-02T16:20:41.727

110

Here's an example for those who have more complicated conditions and using Doctrine 2.* with QueryBuilder:

$qb->where('o.foo = 1')
   ->andWhere($qb->expr()->orX(
      $qb->expr()->eq('o.bar', 1),
      $qb->expr()->eq('o.bar', 2)
   ))
  ;

Those are expressions mentioned in Czechnology answer.

edited May 02 '16 at 16:20

answered Dec 28 '15 at 18:45

Serge Kvashnin

4,332
4
23
37

score 25 · Answer 3 · answered Feb 21 '18 at 13:02

25

One thing missing here: if you have a varying number of elements that you want to put together to something like

WHERE [...] AND (field LIKE '%abc%' OR field LIKE '%def%')

and dont want to assemble a DQL-String yourself, you can use the orX mentioned above like this:

$patterns = ['abc', 'def'];
$orStatements = $qb->expr()->orX();
foreach ($patterns as $pattern) {
    $orStatements->add(
        $qb->expr()->like('field', $qb->expr()->literal('%' . $pattern . '%'))
    );
}
$qb->andWhere($orStatements);

answered Feb 21 '18 at 13:02

leberknecht

1,526
15
27

1

Is this solution safe? What about SQL injection? – pwnz Oct 08 '21 at 11:00
Not sure tbh, this is creating DQL, not SQL, but it might be possible. @paul-attuck pointed out something similar. If you want to stay save, i guess it would be something like `$qb->expr()->like('field', $qb->expr()->literal('%:val_i%'))` and then `$qb->setParameter('val_', $value)` but its likely that you screw up the like-statement with this and dont get what you want. – leberknecht Nov 30 '21 at 15:43
This solution is not safe, is 100% venerable to SQL Injection attack – Adnen Chouibi Jan 23 '23 at 11:58

Czechnology · Answer 4 · 2012-02-01T14:27:03.900

14

Why not just

$q->where("a = 1");
$q->andWhere("b = 1 OR b = 2");
$q->andWhere("c = 1 OR d = 2");

EDIT: You can also use the Expr class (Doctrine2).

edited Feb 01 '12 at 14:27

answered Feb 01 '12 at 11:45

Czechnology

14,832
10
62
88

The question is for doctrine 1.2, the docs you linked is for 2.0. – Maerlyn Feb 01 '12 at 13:24
5

@Maerlyn, well, the question doesn't have the `doctrine-1.2` tag, so I can't be sure which one the user means - in that case I implicitly expect the most current one. – Czechnology Feb 01 '12 at 14:26
My mistake, didn't know there was andWhere and orWhere in doctrine2's query builder. – Maerlyn Feb 01 '12 at 14:32

How to use andWhere and orWhere in Doctrine?

4 Answers4

Linked