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);
	}

}

The Doctrine ORM does a nice job of handling object oriented data, but has a number of flaws when it comes to finding the objects again. Every object it creates is stored in the Table’s repository, but they are not indexed in any meaningful way. It’s pretty much up to you to keep track of your objects once you create them.

What you’d like to do is check to see if a particular object has already been instantiated and not do it again. Recreating the same object multiple times is costly in terms of CPU time even if you use SQL caching.

When it comes to keeping objects around, Doctine is kind of half-pregnant. They are definately there and Doctrine does a good job of making sure you never have duplicate objects floating around that represent the same data in the database. However, finding them is another story.

For example, if you want to see if the User with ID=4 has an object floating around in memory, you can do this:

$repository = Doctrine::getTable(’User’)->getRepository();
foreach ($repository as $u) {
if ($u->getId() == 4) {
do something
}
}

This is great, but imagine if the you have a lot of User objects floating around. This is a very slow way of finding one of them.

What we’d really like is for the repository to be indexed in some meaningful way (or ways), but it’s indexed by a sequential number that’s incremented each time a Doctrine_Record is created. You can get this reasonably useless number by calling $obj->getOID().

If you’ve read a group of objects using the foriegn key or local key relationships of an object, there is another way of finding your objects. All of the references that have been read are stored in an array of reference Doctrine_Collections. To see all of the references for a user object, you can do this:

foreach ($user->getReferences() as $reference)
foreach ($reference as $obj)
(do something with $obj)

You can also get a single reference array using

$collection = $user->reference(’PhoneNumbers’)

where PhoneNumbers is the name of an object set referenced by $user.

These are some basic functions you might find useful. I’ll post later on some of the deficiencies I’ve found in Doctrine relating to handling object associations where the many-to-many is MANY-to-MANY. Doctine is too eager to load EVERYTHING into memory which is find for a few objects, but not so good when it ends up being 10,000.