fmarinheiro fmarinheiro - 1 year ago 108
MySQL Question

Doctrine Update query with a sub-query

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

->update('Table a')
'(SELECT sum(b.amount) FROM Table b WHERE b.client_id = AND b.regular = ? AND b.finished = ?)',
array(false, false))

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 Source

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 = 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download