Who needs disjunctions anyway?



  • The project I'm currently working on uses an ORM called Doctrine which eschews SQL in favour of its own Doctrine Query Language (DQL). The basic syntax for a query is

    Doctrine_Core::getTable('Foo')
      ->createQuery('foo')
      ->where('condition in DQL')
      ->orderBy('bar')
      ->execute();

    But, obviously, for real usage you sometimes have complex conditions. So you can combine them:

    ...
      ->where('X')
      ->orWhere('Y')
      ->andWhere('Z')
    ...

    Now, anyone who's ever done any algebra or parsing knows that you when you have multiple operations you need some way to indicate precedence. So how do you indicate whether you wanted (X OR Y) AND Z or X OR (Y AND Z)? The API seems extremely quiet on the subject.

    The basic answer is that there are never any parentheses, so the above would be X OR Y AND Z and, by standard precedence rules for Boolean algebra, equivalent to X OR (Y AND Z). You might think that it's not so complicated to get (X OR Y) AND Z. Just add the parentheses explicitly:

    ...
      ->where('(X OR Y)')
      ->andWhere('Z')
    ...

    But you'd be wrong. The DQL parser will discard the parentheses as clearly redundant (!)

    It turns out that there is one way which works. Marvel at the beauty:

    ...
      ->where('(1=1') // unbalanced parenthesis
      ->andWhere('X')
      ->orWhere('Y')
      ->andWhere('1=1)') // close that parenthesis
      ->andWhere('Z')
    ...

    (You might also think that you could at least reduce that to

    ...
      ->where('(X')
      ->orWhere('Y)')
      ->andWhere('Z')
    ...

    but, at least in the case where X and Y use subqueries, you'd be wrong. I think it's that when the parser sees a subquery it starts counting parentheses.)



  •  So all those clause methods don't actually do anything useful at all, except add to a stringbuilder or whatever?

    Way to reinvent a triangular LINQ wheel.



  • A clear case of DQL injection.



  • @dhromed said:

    So all those clause methods don't actually do anything useful at all, except add to a stringbuilder or whatever?


    If it were as simple as that it would cause fewer headaches. It tokenises them (badly), does some basic substitutions, and then glues them back together, although sometimes (if there's a join involved) as more than one query.



  • @pjt33 said:

    sometimes (if there's a join involved) as more than one query.

    It just gets better and better, doesn't it? I don't envy you.



  • I love it when somebody ruins a good domain-specific language like SQL by trying to make it "object oriented"..



  •  So is this something where you can use language-native constructs to put together the query - all of those clauses are native function calls which take strings and do their whatevers to construct the execution plan, or is this the string that you construct in whichever language you are using to pass to the DQL engine?  If the former then this isn't terrible, and seems a reasonable approach to a nosql api (so long as the strings/objects that you pass in make sense/don't require rediculous work-arounds to do basic things like override precedense); the more native language constructs it uses over string building the better (table.Foo.query(lambda row: 'columns', 'by', 'name', 'maybe', 'with', 'dynamicly * created - columns').where(lambda row: (X(row) or Y(row)) and Z(row)).sortBy('column').execute(), or sorted((row.col1, row.col2, row.col1+row.col2) for row in table.Foo if (X(row) or Y(row)) and Z(row), lambda r1, r2: cmp(r1.bar, r2.bar)), to provide a couple of pythonic examples).  If the latter then there is only one thing which should be done with it and that is to burn it along with the wooden table it was written on.



  • @airdrik said:

    If the former then this isn't terrible, and seems a reasonable approach to a nosql api (so long as the strings/objects that you pass in make sense/don't require rediculous work-arounds to do basic things like override precedense)

    No, it's retarded. It's just a very inconvenient, incomplete wrapper around SQL. It's like those fucking Java classes that wrap regexes.



  • @morbiuswilters said:

    It's like those fucking Java classes that wrap regexes.
     

    Three problems now?



  • @airdrik said:

    So is this something where you can use language-native constructs to put together the query

    Not really. Those are methods on an object of a class which is part of the ORM library.

    There is one thing which the library does which is convenient, and that's to provide hooks for automatic injection of constraints. For example, it comes with a SoftDelete class and you can configure any table you want to be soft-deletable: it will then add suitable quux.deleted = 0 conditions whenever it builds a query against that table. I just wish it kept the mangling to the minimum required to support its feature set.



  •  I'd be interested to see some of the sql generated by this tool.  ORM's used to butcher the sql languag, and in some cases still do.  This sounds like it might go Vlad Tepesh on the SQL language though.


Log in to reply