CDbCommandBuilder: Update with joins fails on MySQL

Archives Bug Discussions
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?

2

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

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.

4

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

5

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

6

Looking forward to it :)

7

:(

8

Yeah, still very very busy :(

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.

10

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

11

problem is not solved in 1.1.9 ???

12

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

13

do patch have problem at now?

14

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

15

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

16

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