Doctrine andWhere and orWhere usage example [Symfony]

This is an article about Symfony and Doctrine.
Each version of my environment is as follows.
Symfony 3.4
Doctrine 2.12
How do you write the SQL of the Where statement like the following when writing in ORM this time?

WHERE foo = 1 AND (bar = 1 OR bar = 2)

And I would like to explain also when you want to make the OR value and the number of nests variable in PHP.

When the conditions are decided in advance

$q->where("foo = 1")
  ->andWhere("bar = 1 OR bar = 2");

This is simple, just make it an OR statement in andWhere.

You want to receive the condition of the OR statement from a variable or array and specify it.

Even if you simply add orWhere using an if statement, the expected SQL will not be obtained.
In such cases, use Expr Class to describe it.
Reference : Expr Class
See above for details. This time I will use eq.

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

The first example is written using expr class as above.
Next, for the above example, I would like to get the condition part from the array.

// tagList is array
$q->where("foo = 1");
$statements = $qb->expr()->orX();
foreach ($taglist as $tag) {
    $statements->add(
        $qb->expr()->eq('bar', $tag['id'])
    );
}
$qb->andWhere($statements);

From the array called tagList, I assigned the numerical part to be compared as a variable.

Summary

WHERE foo = 1 AND (bar = 1 OR bar = 2)

I think that has a fairly simple structure, but it was quite troublesome to describe it so that it could be changed.
Expr Class has other useful classes, so please take a look at the reference.


Leave a Reply 0

Your email address will not be published.

20 − 10 =