Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

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.

2 comments:

  1. Actually I find this easier than any API-imposed nesting of ANDs and ORs. If you come from SQL (as most of us do) it is relatively straight-forward to write down the query as you did in the "final query" example. Sure, it's not nice to do programmatically, e.g. with a variable number of OR statements that need to glued together.

    But I still remember when we used to do this in Propel, creating criterions anew everytime and chaining AND and OR together, that just produced write-only code.

    So, I'm not really sure if there needs to be improvement. Maybe both variants should be supported, the current DQL approach for writing a static query and a more object-oriented approach for dynamic query generation.

    Cheerio
    Yeti

    ReplyDelete
  2. The thing I dislike about the current Doctrine API is that writing advanced SQL queries is not pure SQL and is not pure Doctrine API ideology, it has to be something between them. And the biggest problem is that this solution is not intuitive (if you haven't seen an example like above). And one of the most important things of any framework or of any tool is to make using it intuitive - and that fails here, so I'd appreciate if something was done here.

    Fortunately, we are not forced to use Propel anymore :) thanks for your comment.

    Cheers
    Tomasz

    ReplyDelete