CDbCommandBuilder: Update with joins fails on MySQL

(Da:Sourcerer) #1

When creating an update command involving joins, the command builder won’t take MySQLs special needs into account: MySQL expects the query to be something like

UPDATE table JOIN table2 ON id=id2 SET column='value'

while the command builder will create something like

UPDATE table SET column='value' JOIN table2 ON id=id2

This can be reproduced pretty quickly within the Yii blog demo:



), array(

	'join'=>'JOIN {{user}} u ON `author_id`=u.`id`',



I realize that this type of query is rather rare. But it’s not entirely impossible. What is leaving me puzzled: There is a patch for this ready since september, seeing very little love. So what is keeping this bug from being fixed?

(Da:Sourcerer) #2

At least getting a reply would be nice. Do yii-devs see this as a non-bug?

(Alexander Makarov) #3

Can’t check it due to lack of time. You can add your example to the ticket so when we’ll get to it we’ll have a code to reproduce.

(Da:Sourcerer) #4

Oh, that example is actually from the ticket. I know, it’s a bit constructed. But it illustrates the problem really well.

(Alexander Makarov) #5

OK, will check it as soon as I’ll have some free time.

(Da:Sourcerer) #6

Looking forward to it :)

(Da:Sourcerer) #7


(Alexander Makarov) #8

Yeah, still very very busy :(

(Pazsitz) #9

Yes, this bug exists also in the 1.1.8 version.

The problem is in the CDbCommandBuilder::createUpdateCounterCommand().

It builds the update and only after then calls the applyjoin() function, which simply appends the join part.

$sql="UPDATE {$table->rawName} SET ".implode(', ',$fields);



The solution can be, build the query yourself and execute it:

$commandBilder = ARModel::model()




$sql = "UPDATE {$table->rawName}";

$sql = $commandBilder->applyJoin($sql, $criteria->join);

$sql .= ' SET ' . implode(', ',$fields);

$sql = $commandBilder->applyCondition($sql, $criteria->condition);

$sql = $commandBilder->applyOrder($sql, $criteria->order);

$sql = $commandBilder->applyLimit($sql, $criteria->limit, $criteria->offset);

$command = $commandBilder->getDbConnection()->createCommand($sql);

$commandBilder->bindValues($command, array_merge($params, $criteria->params));

$result = $command->execute();

something like that.

(Da:Sourcerer) #10

… and in Yii v1.1.9/svn. My patch should still be good, btw.

(Shayan3001) #11

problem is not solved in 1.1.9 ???

(Da:Sourcerer) #12

Yeah, the patch didn’t make it into the last release. Let’s hope for v1.1.10.

(Shayan3001) #13

do patch have problem at now?

(Da:Sourcerer) #14

Can’t say for sure. But my guess is no.

(Da:Sourcerer) #15

Just checked: The patch attached to ticket #2788 is still fine with v1.1.9 and svn.

(Da:Sourcerer) #16

Just to keep you updated: This has been fixed in git/master with commit ed49b77. Thanks again to CeBe :)