Managing parentheses with the Doctrine 2 ORM QueryBuilder

Here is a Doctrine2 ORM snippet for managing parentheses in the WHERE clause of an SQL query using the QueryBuilder.

The target request is:

SELECT m.*, p.*
FROM Message m
INNER JOIN Product p ON m.id = p.mes_id
WHERE  m.delDate IS NULL
AND ( p.module IS NULL OR p.module = 'RAC')


As you will have understood, the problem is at the level of the last line of the SQL query. It would be simple to write in DQL but if you need the abstraction layer of the DQL via the QueryBuilder object, it gets a bit complicated.
To handle parentheses with the Doctrine 2 ORM QueryBuilder object, you cannot directly use the where(), andWhere(), or orWhere() methods. You have to go through the creation of an Expression of type OR in my case. Then all you have to do is add the field/value associations and then add everything to the query with the where() method.


$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder();
 
$qb->from('TestBundle:Message', 'm')
       ->join('m.product', 'p')
       ->where('m.delDate IS NULL');
 
//OR Expression
$orModule = $qb->expr()->orx();
$orModule->add($qb->expr()->eq('p.module', ':module'));
$orModule->add($qb->expr()->isNull('p.module'));
 
//add to query
$qb->andWhere($orModule)
       ->setParameter('module', 'RAC', \PDO::PARAM_STR);
 
$r = $qb->getQuery()->getResult();



It is finally quite simple but I did not find a trace of it in the documentation.

Add a comment

Search

Post's details