Complex WHERE clauses using the PHP Doctrine ORM

Question

I'm using the PHP Doctrine ORM to build my queries. However, I can't quite seem to figure how to write the following WHERE clause using DQL (Doctrine Query Language):

WHERE name='ABC' AND (category1 = 'X' OR category2 = 'X' OR category3 = 'X')  AND price > 10 

How can I specify where the parentheses go?

What I currently have in my PHP code is this:

->where('name = ?', 'ABC') ->andWhere('category1 = ?', 'X') ->orWhere('category2 = ?', 'X') ->orWhere('category3 = ?', 'X') ->andWhere('price > ?', 10) 

But this produces something like

WHERE name='ABC' AND category1 = 'X' OR category2 = 'X' OR category3 = 'X'  AND price > 10 

which, due to order of operations, doesn't return the intended results.

Also, is there a difference between the "where", "andWhere", and "addWhere" methods?

UPDATE Ok, it seems like you can't do complex queries using DQL, so I've been trying to write the SQL manually and use the andWhere() method to add it. However, I'm using WHERE..IN and Doctrine seems to be stripping out my enclosing parentheses:

$q->andWhere("(category1 IN $subcategory_in_clause             OR category2 IN $subcategory_in_clause              OR category3 IN $subcategory_in_clause)"); 

Answer 1

From my experience, each complex where function is grouped within parenthesis (I'm using Doctrine 1.2.1).

$q->where('name = ?', 'ABC')   ->andWhere('category1 = ? OR category2 = ? OR category3 = ?', array('X', 'X', 'X'))   ->andWhere('price < ?', 10) 

produces the following SQL:

WHERE name = 'ABC'    AND (category1 = 'X' OR category2 = 'X' OR category3 = 'X')   AND price < 10 

Answer 2

The correct way of doing this can be found at doctrine 2 - query builder conditional queries... If statements? as noted by @Jekis. Here is how to use the expression builder to solve this like in @anushr's example.

$qb->where($qb->expr()->eq('name', ':name'))   ->andWhere(     $qb->expr()->orX(       $qb->expr()->eq('category1', ':category1'),       $qb->expr()->eq('category2', ':category2'),       $qb->expr()->eq('category3', ':category3')   )   ->andWhere($qb->expr()->lt('price', ':price')   ->setParameter('name', 'ABC')   ->setParameter('category1', 'X')   ->setParameter('category2', 'X')   ->setParameter('category3', 'X')   ->setParameter('price', 10);