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

As for the difference between where, andwhere, and addwhere, i don't believe there is a significant difference from the last time i read the source. I would encourage you to read the Doctrine source, however. It's really simple, and helps fills in the holes in the documentation (there are many). As for complex where statements, I've wondered this myself but haven't had a need for it yet.

Answer 2

As it seems like you can't do complex queries using DQL, I wrote the following SQL to pass to the andWhere() method:

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

Note the "AND TRUE", a hack so that the parser wouldn't ignore the outer parentheses.

Answer 3

I have a solution posted on my blog that involves a simple subclass of Doctrine_Query.

http://danielfamily.com/techblog/?p=37

Answer 4

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