Yii INSERT ... ON DUPLICATE UPDATE

Question

I am working on a Yii project. How can I use the ON DUPLICATE feature of MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ) when doing a 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 a 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 don't want it to complain, I just want it to update with the latest data.

I know how to do it in raw SQL, I was just wondering if there is a clean Yii way to do it.

Solution 1

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

Seems to work. Not sure how performant it is.

Solution 2

You are using models in Yii, its quite simple .. try to load you model where you suspect to have duplicate entries, if you find the entry the model is loaded else null is return. now if your model is null simply create new model. rest is your normal code to insert a new record.

//try to load model with available id i.e. 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 post controllers update method in sample app Yii blog. I might be wrong with spelling of function names, sorry for that.

Solution 3

I'm repeating two main points from previous answers I think you should keep:

1. Don't (try to) use "on duplicate key update" since its MySQL-only, as txyoji points out.

2. Prefer the select->if not found then insert->else insert demonstrated by Uday Sawant.

There's another point here, though: Concurrency. Although for low traffic applications the probability that you'll get in trouble is minimal (still never zero), I think we always be careful about this.

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

Here's a bad scenario:

1. You do select your record using findByPk() which returns null
2. Some other transaction (from some other user request) inserts a record with the id you just failed to select
3. At the next instant you try to insert it again

In this case you'll either get an exception (if you're working with a unique key, as you do here) or a duplicate entry. Duplicate entries are much harder to pick up (usually nothing seems weird until your users see duplicate records).

The solution here is to set a strict isolation level, for example "serializable", and then begin a transaction.

Here's 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 i.e. 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 see more about isolation levels at least in the following links and see what every isolation level has to 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

Solution 4

The "On Duplicate Key Update" feature is specific to MySQL's dialect of SQL. Its unlikely to be implemented in any data abstraction layer. ZendDB and Propel don't have an equivalent.

You can simulate the behavior by attempting an insert in a try/catch and update if insert fails with the proper error code. (duplicate key error).

Solution 5

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

You can extend the save() method of the model to look for an existing record, and update it, or insert a new row if it doesn't.

View more solutions

Comments

[Dan Blows]

Upvote for the answer to the problem, downvote for the solution. Try/catch is really for error handling, and this situation isn't an error. See stackoverflow.com/questions/1159665/…

[thomaux]

I would give you a +1, but a try catch to implement logic is a REALLY bad idea

[txyoji]

Under the hood, this is exactly what MySQL is doing. It attempts the insert, fails and does the update. I don't see the problem. In this case, the active record class will generate an exception on insert if the key already exists.

[thomaux]

@txyoji if what you're saying is correct, someone should go and slap some MySQL devs. Try/Catch should never be used to create a logical flow in your code!

[Dan Blows]

@txyoji The problem is that it becomes tightly coupled to the implementation of the model. If the model stops using ActiveRecord, or for some other reason stops throwing an exception, the try/catch block will no longer work.

[txyoji]

@Blowski - Point taken. Thanks for the feedback.

[Dan Blows]

It works, but it kind of breaks the 'Principle of least astonishment' - validating does not usually involve finding out whether to do an update or insert. It should simply say whether or not 'this set of data passes the validation rules'.

[Anand]

when doing findbypk YII is internally doing a 'select' query, I dont think that was the purpose of the question.

[Uday Sawant]

Yes, Yii makes a select call on findByPk(). The purpose of the question was to update a row if it already exist or insert a new one. And while using models this is how it goes. Of course its a performance penalty, but if you don't want to override Yii this is the best available option. Please come up with any better alternative, that will be really helpful. Thanks dude...

[Nathan H]

But it is "before" validate. So it's just a way to run code before things get checked.

[Snivs]

Unfortunately, this works only for primary keys getting duplicate entries, not for checking handling other kinds of constraint violations by duplication.

[Mohammed H]

What @Anand said is correct. He is asking how to implement ON-DUPLICATE-UPDATE.

[Kshitiz]

Help me with following. if there is a update query after $trn->commit(); in above transaction. Will it execute?

[shasi kanth]

I think Laravel might address this issue much better than YII 1