Scott’s Technical Blog

I really like Doctrine. It’s important to start my posts with this comment because many of my posts are about it’s limitations and how to overcome them, not about it’s nice features. One of the worst limitations is the inability to wrap complex WHERE statements in parenthesis. DQL relies almost entirely on the precedence of AND and OR. This is great for simple stuff, but it falls apart pretty quickly if your where statement is complex.

Even something as simple as “(ConditionA OR ConditionB) AND ConditionC” can be difficult. In DQL,

$query
->where('ConditionA')
->orWhere('ConditionB')
->andWhere('ConditionC')


will result in “ConditionA OR ConditionB AND ConditionC”. AND takes precedence over OR and the result is “ConditionA OR (ConditionB AND ConditionC)”. That is NOT what we wanted.

The solutions I’ve seen posted suggest wrapping the entire phrase in a single DQL where statement. For example,

$query
->where("(ConditionA OR ConditionB) AND ConditionC");


This works sometimes. It works until the level of complexity of the DQL statement makes Doctrine’s parser spit up blood. I can’t tell you exactly when that will happen, but I’ve encountered it enough times that it had become a serious issue.

My ultimate solution (pay attention, this is the magic part) was to figure out how to inject specific parenthesis into the DQL stream. I looked through the Doctrine source code (I’m using the version distributed with Symfony 1.2) and tried to find a good hook. This was probably my third time through the entire DQL parsing engine so I was starting to feel at home.

I had already created a subclass of Doctrine_Query called DQ (my Dairy Queen class). It has a couple of handy methods in it like andWhereIf() and orWhereIf() that made my life easier. To fix the parenthesis problem, I added another method called whereParamWrap().

To use the DQ class, simply substitute DQ for Doctrine_Query when creating a query. For example,

DQ::create()
->from('ImportantTable i')
->where('i.field = ?', 1)
->andWhereIf($filter);


In this case, the andWhereIf($filter) will be ignored if $filter is empty().

To use the parenthesis magic, you’d create a query like this

DQ::create()
->from('ImportantTable i')
->where('i.field = 1')
->orWhere('i.otherfield = 2')
->whereParenWrap()
->andWhere('i.finalfield = 3');


whereParenWrap() will wrap parenthesis around all of the where conditions collected so far. Additional where conditions will be outside the parens. The result is (i.field = 1 OR i.otherfield = 2) AND i.finalfield = 3. The DQL elite would now point out that I could have simply built that condition into the initial where clause and saved myself the trouble of spending a day figuring this paren thing out. In reply, I would say to try this as one statement:

DQ::create()
->from('Foo f')
->leftJoin('f.FooData d')
->leftJoin('f.UserA s')
->leftJoin('f.UserB p')
->leftJoin('f.Group g')
->where("f.group_id IN (SELECT guA.group_id as group_id FROM GroupHasUser guA WHERE guA.user_id = $uid AND guA.status in ($members))")
->andWhere("f.group_id IN (SELECT guB.group_id as group_id FROM GroupHasUser guB WHERE guB.user_id = $cid AND guB.status in ($members))")
->orWhere("f.primary_user_id = $uid AND g.view_feeds = " . Group::EVERYONE)
->orWhere("f.primary_user_id = $uid AND f.secondary_user_id = $cid")
->orWhere("f.primary_user_id = $cid AND f.secondary_user_id = $uid")
->whereParenWrap()
->andWhere('f.type in ?', $types);


The whereParenWrap is particularly useful if you have to pass the query around to have additional conditions added to it. You can wrap all of the initial statements in parenthesis so they are treated autonomously. For example,

function getQuery() {
$query = DQ::create()
->from('ImportantTable i')
->where('i.field = 1')
->orWhere('i.otherfield = 2')
->whereParenWrap();


return $query;
}


$query = getQuery();
$query->andWhere(’i.importantfield = 3′);


Here’s the class. Use it at your own risk. It worked for me, but no promises.

/*
 * This is a simple short-hand wrapper for Doctrine_Query. It provides
 * a shorter class name and a few additional functions.
 */
class DQ extends Doctrine_Query
{
	/**
	 * Returns a DQ object to get started
	 *
	 * @return DQ
	 */
	public static function create($conn = null) {
		return new DQ($conn);
	}

	/**
	 * This function will wrap the current dql where statement
	 * in parenthesis. This allows more complex dql statements
	 * It can be called multiple times during the creation of the dql
	 * where clause.
	 *
	 * @return $this
	 */
	public function whereParenWrap() {
		$where = $this->_dqlParts['where'];
		if (count($where) > 0) {
			array_unshift($where, '(');
			array_push($where, ')');
			$this->_dqlParts['where'] = $where;
		}

		return $this;
	}            

	/**
	 * Create and andWhere if the where parameter is not empty
	 *
	 * @param string $where where string
	 * @param parameters $params
	 *
	 * @return DQ this object
	 */
	public function andWhereIf($where, $params = array()) {
		return empty($where)
			? $this
			: $this->andWhere($where, $params);
	}

	/**
	 * Create and orWhere if the where parameter is not empty
	 *
	 * @param string $where where string
	 * @param parameters $params
	 *
	 * @return DQ this object
	 */
	public function orWhereIf($where, $params = array()) {
		return empty($where)
			? $this
			: $this->orWhere($where, $params);
	}

}