fmarinheiro fmarinheiro - 9 days ago 6
MySQL Question

Doctrine Update query with a sub-query

I'm trying to execute a query, similar to the following one, using doctrine dql:

Doctrine_Query::create()
->update('Table a')
->set('a.amount',
'(SELECT sum(b.amount) FROM Table b WHERE b.client_id = a.id AND b.regular = ? AND b.finished = ?)',
array(false, false))
->execute();


But it rises a Doctrine_Query_Exception with the message: "Unknown component alias b"

Is restriction about using sub-queries inside the 'set' clause, can you give me some help?

Thanks in advance.

Tom Tom
Answer

I'm not sure if there's a restriction on this but I remember fighting with this sometime ago. I eventually got it working with:

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$q->execute("UPDATE table a SET a.amount = (SELECT SUM(b.amount) FROM table b WHERE b.client_id = a.id AND b.regular = 0 AND b.finished = 0)");

See if that does the trick. Note that automatic variable escaping doesn't get executed with this query as it's not DQL.