How to group more andWhere, orWhere in Doctrine

Question

SELECT * FROM dg
WHERE 
     ( a < 1 AND b > 1)
  OR ( a > 1 AND ( 
                     (c = 3 AND B < 2) 
                  or (c = 4 AND B < 5 ))
     )

I don't sure how to properly group more andWhere and orWhere. I've found a example for group more AND but not example for OR.
For exp. WHERE a=1 AND (a>1 Or b=2) AND (a>1 OR c=2) working query is:

public function myQuery()
{
    return $this->createQueryBuilder( 'dg' )
                ->where("a = 1")
                ->andWhere("a > 1 OR b = 2")
                ->andWhere("a > 1 OR c = 3")
                ->getQuery()
                ->getResult()
        ;
}

How can I use my SELECT in Doctrine2 to create Query Builder?

Answer 1

For grouping and hierarchy of or/and and the like, you can link and's and or's using querybuilder's ->expr() method chaining onto ->andX() and ->orX() respectively on your instance of QueryBuilder. You can check here for more information: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#the-expr-class

Basically you'll get something like the following to translate your second statement:

// In this example I'll be assuming that 'dg' is an entity
// and that 'a', 'b' and 'c' are its attributes
// since, remember, Doctrine is designed specifically for using entities
// and make abstraction of the whole table model in your database

// First we'll create your QueryBuilder instance $qb
$qb = $this->createQueryBuilder('dg');

// Then we add our statements to the QueryBuilder instance
$qb
    ->where($qb->eq('dg.a', 1))
    ->andWhere($qb->expr()->orX(
        $qb->expr()->gt('dg.a', 1),
        $qb->expr()->eq('dg.b', 2)
    ))
    ->andWhere($qb->expr()->orX(
        $qb->expr()->gt('dg.a', 1),
        $qb->expr()->eq('dg.c', 3)
    ))
;

// Now you can use the QueryBuilder instance to, for instance, 
// have it do getResult (which in this case will return an array of 'dg' entities)
return $qb->getQuery()->getResult();

You can put orX()'s and andX()'s into other orX()'s and andX()'s as well, and you can add an arbitrary number of conditions in your andX() and orX(), to create very complex queryies.

Have fun :)