Complex WHERE clauses using the PHP Doctrine ORM
doctrine, dql, mysql, php
Solution
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
Problem
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)"); ```