Advanced SQL expressions in Doctrine

Scene from "Irma la Douce" by Billy Wilder (1963)

missing element in symfony documentation

Symfony and Doctrine book (chapter 6) describes DQL API, but one very important SQL feature is missing: advanced expressions. Particularly, you may often need to use advanced logical expressions in WHERE clause.

example

For example, we run a cron task searching for all active posts which does not meet all SEO requirements - then a warning mail is sent to a particular employee of a company to do something with it. So the query needs to look for Post objects:

Doctrine_Query::create()->from('Post p')

which are active:

->where('p.active = 1')

and have invalid SEO data at the same time, let's assume that SEO is invalid when at least one of all SEO data columns is empty (title, keywords and description):

->orWhere('LENGTH(p.meta_description) = 0')
->orWhere('LENGTH(p.meta_keywords) = 0')
->orWhere('LENGTH(p.meta_title) = 0') 

Now take a look at the above code - is that correct? Of course not! We want to generate the following query:

SELECT *
FROM Post p
WHERE p.active = 1
AND (
  LENGTH(p.meta_description) = 0 OR 
  LENGTH(p.meta_keywords) = 0 OR 
  LENGTH(p.meta_title) = 0)

Three SEO alternatives need to be enclosed in parenthesis. But Doctrine Query API does not provide specific methods doing that. Fortunately, we may use standard query methods (where, orWhere, andWhere, etc.). Unfortunately, we can use them only on the top-level of the query (meaning that all we write stays outside parenthesis). So the top-level of query is created by two arguments linked with AND logical operator - each of those arguments use one DQL API where method. But the second argument (invalid SEO) is internally ivided into an alternative of three subarguments, using OR logical operator. The final query looks like:

$objects = Doctrine_Query::create()
  ->from('Post p')
  ->where('p.active = 1')
  ->andWhere(
  'LENGTH(p.link_rewrite) = 0 OR '.
  'LENGTH(p.meta_description) = 0 OR '.
  'LENGTH(p.meta_keywords) = 0 OR ')
  ->execute();

Anyway, it works. Maybe the Doctrine API will be more friendly in Symfony 2.