Search code examples
sqldoctrinedqlparentheses

Doctrine - strange moved parenthesis in the query

I have tested and done quite some research online, but still no luck. Did anyone ever encounter this problem ?

Say, I have a doctrine query set up like:

$q = Doctrine_Query::create()
->update('PckFolder')
->set('id_path', "CONCAT(?, RIGHT(id_path, LENGTH(id_path)-?))", array($newPath, $lenOld))
->where("id_path like '$oldPath%'");

// and I print the query out
$qstr = $q->getSqlQuery(array($newPath, $lenOld));

Instead of giving me:

UPDATE pck_folder SET id_path = CONCAT(?, RIGHT(id_path, LENGTH(id_path)-?)) WHERE (id_path like '1/2//%')

Doctrine gave me:

UPDATE pck_folder SET id_path = CONCAT(?, RIGHT(id_path, LENGTH(id_path-?))) WHERE (id_path like '1/2//%')

please note this part RIGHT(id_path, LENGTH(id_path)-?)

Solution

  • (Note: I'm assuming you're using Doctrine 1.2. I haven't used Doctrine 2.0 yet.)

    I had not encountered that specific bug before, but I have found numerous problems with the implementation of update() in Doctrine_Query. Essentially anything but the most very straightforward update queries will cause the parser to generate wrong or invalid queries. For example, it can't handle sub-selects within an update.

    Try writing a Raw SQL query, or else use a less efficient but fully-functional workaround: Select the records you want to update using Doctrine_Query, then iterate over them and set the field in PHP, then call save() on each one.

    By the way, there's a big GOTCHA inherent with use of UPDATE queries and Doctrine that sort of forces you to use that workaround in many cases anyway. That is, if you or your plugins have made use of the nifty Doctrine hook methods within your models but you execute a SQL-level update that affects those records, the hooks will get silently circumvented. Depending on your application, that may wreck your business logic processing.