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:




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) #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);

$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) #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 :)