CODEWITHSUNDEEP
yii
Akim Kelar
Akim Kelar

Reputation: 635

Yii update with join

I have a code

$command = Yii::app()->db->createCommand()
        ->update(
            'queue q',
            array('i.status_id' => $status_id)
        )
        ->join('item i', 'q.item_id = i.item_id')
        ->where('IN', 'queue_id', $ids);

after I call $command->buildQuery() I get an error:

CDbCommand failed to execute the SQL statement: Invalid parameter number: parameter was not defined. The SQL statement executed was: UPDATE queue q SET i.status_id=:i.status_id

The impression is that it does not see the join and where commands. What the problem?

Upvotes: 0

Views: 3446

Answers (4)

Arth
Arth

Reputation: 13110

Having come across this problem a few times in my projects I have come-up with the following Yii work-around using CDbCriteria which is a little hacky, but gives the security of param count matching.

When applied to your example my code would be (guessing a little bit of your structure):

$ids = array(1,2,3,4,5);
$criteria = new CDbCriteria();
$criteria->addInCondition('i.queue_id',$ids);

$sql = '
  UPDATE queue q
    JOIN item i 
      ON q.item_id = i.item_id
     SET i.status_id = :status
   WHERE '.$criteria->condition;

$command = Yii::app()->db->createCommand($sql);
$command->bindValue('status',$status);
$command->bindValues($criteria->params);
$rows = $command->execute();

Upvotes: 0

Jakob Stoeck
Jakob Stoeck

Reputation: 624

Your code is valid with the newest Yii version. This MySQL-specific functionality has been added as of 1.1.14: https://github.com/yiisoft/yii/commit/ed49b77ca059c0895be17df5813ee1e83d4c916d.

Upvotes: 1

Puigcerber
Puigcerber

Reputation: 10094

You have to bind the parameters:

$command = Yii::app()->db->createCommand()
    ->update(
        'queue q',
        array('i.status_id' => ':status_id'),
        array('in', 'queue_id', $ids),
        array(':status_id' => $status_id),
    )
    ->join('item i', 'q.item_id = i.item_id');

Upvotes: 0

dInGd0nG
dInGd0nG

Reputation: 4114

The where clause should be in the update() function like this

 Yii::app()->db->createCommand()
        ->update(
            'queue q',
            array('i.status_id' => $status_id),array('in', 'queue_id', $ids)
        );

And regarding the JOIN part there is a open bug at https://github.com/yiisoft/yii/issues/124 (Im not sure. Correct me if Im wrong). Please let me know if there is a workaround.

Upvotes: 0

Related Questions