Yii INSERT ... UPDATING DUPLICATE

Question

I am working on a Yii project. How can I use the ON DUPLICATE function of MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ) when doing save () on a Yii model?

My MySQL is as follows:

CREATE TABLE `ck_space_calendar_cache` ( `space_id` int(11) NOT NULL, `day` date NOT NULL, `available` tinyint(1) unsigned NOT NULL DEFAULT '0', `price` decimal(12,2) DEFAULT NULL, `offer` varchar(45) DEFAULT NULL, `presale_date` date DEFAULT NULL, `presale_price` decimal(12,2) DEFAULT NULL, `value_x` int(11) DEFAULT NULL, `value_y` int(11) DEFAULT NULL, PRIMARY KEY (`space_id`,`day`), KEY `space` (`space_id`), CONSTRAINT `space` FOREIGN KEY (`space_id`) REFERENCES `ck_space` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

My PHP is as follows:

 $cache = new SpaceCalendarCache(); $cache->attributes = $day; //Some array with attributes $cache->save(); 

If there is a duplicate in my primary key (sapce_id, day), I do not want to complain, I just want it to be updated with the latest data.

I know how to do this in raw SQL, I'm just wondering if there is a Yii way to do this.

Answer 1

I redid beforeValidate () where I checked if a duplicate exists. If so, I set $this->setIsNewRecord(false);

It seems to need work. Not sure how this is possible.

Answer 2

You use models in Yii, it's pretty simple. try loading the model where you suspect that you have duplicate entries, if you find that the model is loaded, else null is the return. now if your model is null just create a new model. rest is your regular code to insert a new record.

 //try to load model with available id ie unique key $model = someModel::model()->findByPk($id); //now check if the model is null if(!$model) $model = new someModel(); //Apply you new changes $model->attributes = $attributes; //save $model->save(); 

Refer to the postcontroller update method in the sample Yii blog application. Maybe I'm wrong in spelling function names, sorry for that.

Answer 3

I repeat two main points from the previous answers that I think you should keep:

• Do not try to use "when re-updating the key" with its MySQL-only, as txyoji points out.

• Prefer select->if not found, then insert->else paste, demonstrated by Uday Sawant.

Here's another point: Concurrency. Although for low-traffic applications, the likelihood that you will have problems is minimal (still never equal to zero), I think we will always be careful about this.

From a transactional point of view, "INSERT .. ON DUPLICATE UPDATE" not equivalent to selecting in your application memory and then inserting or updating. The first is one transaction, and the second is not.

Here's a bad scenario:

• You select your entry using findByPk() , which returns null
• Some other transactions (from some other user request) insert a record with an identifier that you simply could not select
• The next moment you try to insert it again

In this case, you will either get an exception (if you are working with a unique key, as you are here), or duplicated. Duplicating entries is much more difficult (usually nothing seems strange until your users see duplicate entries).

The solution here is to set a strict isolation level, such as "serializable", and then start the transaction.

Here is an example for yii:

 Yii::app()->db->createCommand('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); $trn = Yii::app()->db->beginTransaction(); try { // Try to load model with available id ie unique key // Since we're in serializable isolation level, even if // the record does not exist the RDBMS will lock this key // so nobody can insert it until you commit. // The same shold for the (most usual) case of findByAttributes() $model = someModel::model()->findByAttributes(array( 'sapce_id' => $sapceId, 'day' => $day )); //now check if the model is null if (!$model) { $model = new someModel(); } //Apply you new changes $model->attributes = $attributes; //save $model->save(); // Commit changes $trn->commit(); } catch (Exception $e) { // Rollback transaction $trn->rollback(); echo $e->getMessage(); } 

You can learn more about isolation levels at least in the following links and see what each isolation level can offer in data integrity in exchange for concurrency

http://technet.microsoft.com/en-us/library/ms173763.aspx

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Answer 4

The "On Duplicate Key Update" function refers to the SQL SQL dialog. It can hardly be implemented at any level of data abstraction. ZendDB and Propel have no equivalent.

You can model the behavior by trying to insert into try / catch and update if the insert failed with the correct error code. (repeated key error).

Answer 5

I agree with the analysis of @txyoji problem, but I would use a different solution.

You can extend the save() method of the model to search for an existing record, update it, or insert a new row if it does not work.

Answer 6

you need to use try catch:

  try{ $model->save(); } catch(CDbException $e){ $model->isNewRecord = false; $model->save(); }