Putting parentheses into complex doctrine sql queries

Consider this query:

SELECT * FROM mytable WHERE
  ( status IN ('A','B') OR access IN ('X','Y') )
  AND created_at > '2010-01-01';

Doctrine offers no way to do this! The following code makes a query without the parentheses, meaning it will return all records with status of A or B, whatever their creation date.

$q=Doctrine_Query::create() ->from('mytable');
$q->whereIn('status',$statusList);
$q->orWhereIn('access',$accessList);
$q->andWhere('created>?','2010-01-01');

If your IN lists are of fixed length you could do it like this (untested):

$q=Doctrine_Query::create() ->from('mytable');
$q->where('( status IN (?,?) OR access in (?,?) )',array('A','B','X','Y'));
$q->andWhere('created>?','2010-01-01');

But a far better solution is suggested on Scott Daniel's blog

I changed his code in two ways. First I put it straight into Doctrine/Query.php, which saves me having to change the class name in my source code. I'm comfortable doing this as I have the Doctrine source in SVN, meaning I won't lose my change when updating Doctrine. Secondly I used an explicit reference to save having to reassign the variable. I doubt there is any difference in speed, but this way looks clearer to me. So the full code I put at the end of Query.php looks like this:

/**
* Custom addition to Doctrine, to allow wrapping a set of OR clauses
* in parentheses, so that they can be combined with AND clauses.
*
* @see http://danielfamily.com/techblog/?p=37
* I modified it slightly to use an explicit reference.
*
* @return Doctrine_Query this object
*/
public function whereParenWrap()
{
  $where = &$this->_dqlParts['where'];
  if (count($where) > 0) {
    array_unshift($where, '(');
    array_push($where, ')');
    }
  return $this;
}

To use it simply add a call to whereParenWrap() just after your OR clauses. For instance, here is how my original example is modified:

$q=Doctrine_Query::create() ->from('mytable');
$q->whereIn('status',$statusList);
$q->orWhereIn('access',$accessList);

$q->whereParenWrap();

$q->andWhere('created>?','2010-01-01');

In my actual code this required me to shuffle things around so the OR clauses were defined first, then the straightforward AND clauses came last. But that was no hardship.

However there is one flaw in this approach: if you need to have two such sets of OR clauses, which are themselves joined by an OR or AND. I thought a more generic approach might work: openParenthesis() and closeParenthesis() functions which you call exactly where you want them. But openParenthesis goes wrong (it ends up making "WHERE ((AND status IN..."; i.e. the "(" looks like an existing clause to Doctrine). I'm sure this could be made to work, but it will be more intrusive. So I'm going to be pragmatic and worry about this should I ever actually need it.

Doctrine many-to-many relationships with onDelete

In a previous article I explained why I now try to put an explicit onDelete on every relation. The problem comes with many-to-many relationships. If you put onDelete: CASCADE in the relationship it gets ignored and the entries survive in your refclass table. I found some page on this but none that really answers the question fully:

 http://stackoverflow.com/questions/1488171/in-symfony-doctrines-schema-yml-where-should-i-put-ondelete-cascade-for-a-many

 http://groups.google.com/group/doctrine-user/browse_thread/thread/2f924ee3b8cd2689?pli=1

 http://trac.doctrine-project.org/ticket/1123

The common theme is if you want an onDelete on a many-to-many relationship then you have to put it on the reference table. But not a single example anywhere. So, let's start with the "friends" nest example in the manual.

Here is what does not work (no error, simply leaves the records behind in FriendReference)

User:
# ...
  relations:
    # ...
    Friends:
      class: User
      local: user1
      foreign: user2
      refClass: FriendReference
      equal: true
      onDelete: CASCADE

FriendReference:
  columns:
    user1:
      type: integer
      primary: true
    user2:
      type: integer
      primary: true


This one won't even compile (well, I didn't think it would):

User:
# ...
  relations:
    # ...
    Friends:
      class: User
      local: user1
      foreign: user2
      refClass: FriendReference
      equal: true

FriendReference:
  columns:
    user1:
      type: integer
      primary: true
    user2:
      type: integer
      primary: true

  relations:

     onDelete: CASCADE



So, how do we change it so it will work? I'll update this if and when I work it out; if you know please let me know.

Be explicit about OnDelete in doctrine schemas

In doctrine, I tried $obj->delete(); and got an SQL exception from another table. But you're supposed to have deleted that too, that's the whole point of me telling you about all the relations between the tables, screamed I.

So I went through my schema inserting "onDelete: CASCADE" in every relation. But as I did it realized sometimes I wanted "onDelete: SET NULL", and sometimes I wanted "onDelete: RESTRICT". This is a helpful article on the onDelete choices (though for null you actually have to write "SET NULL" not just "NULL". There is also another choice: "OnDelete: SET DEFAULT" (e.g. when a user is deleted, you may want his comments to become owned by the admin user, rather than become orphaned.)

So, now my advice is to explicitly put onDelete on every relation; treat it as required. I also find I need a line of documentation to explain every time I choose something other than cascade. It is good to be forced to think of these things.

All is smooth until you come to many-to-many relationships, especially self-referential (nest) relationships, such as two users being friends (symmetrical) or one user blocking another (one-way). I will cover it in a separate article.