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)");
Best Answer
From my experience, each complex
where
function is grouped within parenthesis (I'm using Doctrine 1.2.1).produces the following SQL: