Doctrine Update query with a sub-query

Question

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.

Answer 1

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.