A bulletproof pattern for creating Doctrine subqueries of any complexity

Doctrine subqueries can be very frustrating. They sometimes work but as soon as you reach a certain complexity level, Doctrine just can't handle things anymore. I will show you how to write subqueries in Doctrine which you can nest in as many levels as you want without Doctrine complaining about it and still using the DQL (Doctrine Query Language).

Notice that this post refers to Doctrine Version 1.2. Version 2 of Doctrine is already released but many developers still use symfony 1.3/1.4 which ships with Doctrine 1.2.

A lot of people suggest embedding subqueries into the where() function of Doctrine but this will only work until a certain level. At some point Doctrine gets confused parsing the subqueries. This results in queries that are not completely parsed and thus throw errors when executed. The solution is to explicitly tell Doctrine about the subquery. I am going to use an example query which is short enough to understand the solution pattern. Lets say I have two tables, one table for movies and and one for movies I already saw. In my query I want to check if the movie "Prometheus" (in a Movie-Table) is one of the movies I already saw (by checking if the name exists in a SeenMovie-Table). Lets have a look at the sample query:

// nest subquery into the where condition
$q = Doctrine_Query::create()
  ->from('Movie m')
  ->where('name = ?', 'Prometheus')
  ->where('EXISTS (SELECT * FROM SeenMovie sm WHERE m.name=sm.name)')

We have a simple select with a nested subselect in the where condition. I know that this is not very complex and Doctrine can surely handle this. But it is easier to understand the principle using this simple example (and I like the movie "Prometheus" :-P). You can then easily use the pattern on more complex queries. So here is how to tell Doctrine about the nested subquery:

// build root query
$query = Doctrine_Query::create()
  ->from('Movie m')
  ->where('name = ?', 'Prometheus')

// build subquery
$subquery = $query->createSubquery()
  ->from('SeenMovie sm')
  ->where('m.name = sm.name')

// nest subquery and execute
$query->where('EXISTS (' . $subquery->getDql() . ')')->execute();

What I did is, I created the subquery separately from the main query by using $query->createSubquery(). A new subquery is created and linked to the main query. This replaces Doctrine_Query::create() when creating regular queries. Everything after $query->createSubquery() is exactly the same as if you were writing a regular query. So you can write your subquery just like any other query. You can even nest another subquery by continuing the pattern. Then you call $subquery->getDql() to let Doctrine "render" the subquery. After that you can embed it into the main query and execute it. The result is the same as before but you excplicitely told Doctrine about the subquery. This helped me in a much more complex situation and I'm sure it will help you too :-)

comments powered by Disqus