[Da:Sourcerer]

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:

Post::model()->updateAll(array(
	'content'=>'m00',
), array(
	'join'=>'JOIN {{user}} u ON `author_id`=u.`id`',
	'condition'=>'u.`username`="demo"',
));

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]

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

[samdark]

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]

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

[samdark]

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

[Da:Sourcerer]

Looking forward to it

[Da:Sourcerer]

[samdark]

Yeah, still very very busy

[PazsitZ]

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);
$sql=$this->applyJoin($sql,$criteria->join);
...

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

$commandBilder = ARModel::model()
    ->getDbConnection()
    ->getSchema()
    ->getCommandBuilder();

$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]

PazsitZ, on 29 December 2011 - 05:51 AM, said:

Yes, this bug exists also in the 1.1.8 version.

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

[shayan]

problem is not solved in 1.1.9

[Da:Sourcerer]

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

[shayan]

Da:Sourcerer, on 03 January 2012 - 08:36 AM, said:

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

do patch have problem at now?

[Da:Sourcerer]

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

[Da:Sourcerer]

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

[Da:Sourcerer]

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