Yii Framework Forum: CDbCommandBuilder: Update with joins fails on MySQL - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CDbCommandBuilder: Update with joins fails on MySQL Rate Topic: -----

#1 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 09 November 2011 - 07:17 AM

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?
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
1

#2 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 16 November 2011 - 07:08 AM

At least getting a reply would be nice. Do yii-devs see this as a non-bug?
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#3 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,343
  • Joined: 17-January 09
  • Location:Russia

Posted 16 November 2011 - 04:29 PM

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.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#4 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 16 November 2011 - 04:34 PM

Oh, that example is actually from the ticket. I know, it's a bit constructed. But it illustrates the problem really well.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#5 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,343
  • Joined: 17-January 09
  • Location:Russia

Posted 16 November 2011 - 06:08 PM

OK, will check it as soon as I'll have some free time.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#6 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 16 November 2011 - 06:25 PM

Looking forward to it :)
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
1

#7 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 19 December 2011 - 11:54 AM

:(
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#8 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,343
  • Joined: 17-January 09
  • Location:Russia

Posted 20 December 2011 - 10:35 AM

Yeah, still very very busy :(
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#9 User is offline   PazsitZ 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 28-July 11
  • Location:Hungary

Posted 29 December 2011 - 05:51 AM

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

#10 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 29 December 2011 - 06:38 AM

View PostPazsitZ, 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.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#11 User is offline   shayan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 53
  • Joined: 10-October 11

Posted 01 January 2012 - 11:17 AM

problem is not solved in 1.1.9 ???
0

#12 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 03 January 2012 - 08:36 AM

Yeah, the patch didn't make it into the last release. Let's hope for v1.1.10.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#13 User is offline   shayan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 53
  • Joined: 10-October 11

Posted 04 January 2012 - 10:43 AM

View PostDa: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?
0

#14 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 04 January 2012 - 11:16 AM

Can't say for sure. But my guess is no.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#15 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 30 January 2012 - 10:14 AM

Just checked: The patch attached to ticket #2788 is still fine with v1.1.9 and svn.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#16 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,221
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 30 August 2012 - 02:02 AM

Just to keep you updated: This has been fixed in git/master with commit ed49b77. Thanks again to CeBe :)
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users