CActiveRecord.updateAll using join in where clause

Yii 1.1 General Discussion
1

Hi everybody!

I’m trying to update some records, using CActiveRecord.updateAll, but I have to use a join in my where clause and it’s giving me some trouble…

I’ve tried this




MyModel::model()->with('other_model om')->updateAll(array('user_id'=>'NULL'), 'om.my_model_id = '. $this->my_model_id .' and user_id=7 ');

And it’s throwing me this error…




CDbCommand::execute() failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'om.my_model_id' in 'where clause'. The SQL statement executed was: UPDATE `my_model` SET `user_id`=:yp0 WHERE om.my_model_id = 8 and user_id=7.

Here you can see it’s not doing any joins on the update statement… what’s the best way to do this?

thanks in advance!

2

Method updateAll don’t support relations.

As I remember, different databases use custom SQL syntax for updates with join - someone don’t support joins on update.

As example, you can use subquery or write SQL procedure.

3

Better use DAO or try the query builder:


Yii::app()->db->createCommand('UPDATE ...')->execute();
4

Thanks for your responses!

I’ve tried this way, and it works fine!

Cheers!