Use REPLACE instead of INSERT on AR save()

I have a table that has a combined unique key and would like to use a REPLACE statement when a record is saved.

For example, at some point this happens:





$model=new Model;

$model->uniqueKeyOne='A';

$model->uniqueKeyTwo='B';

$model->uniqueKeyThree='C';

$model->save();




And at another time it happens again:





$model=new Model;

$model->uniqueKeyOne='A';

$model->uniqueKeyTwo='B';

$model->uniqueKeyThree='C';

$model->save();




Of course the second time it fails. I know the solution would be to do this:





$model=Model::model()->find("uniqueKeyOne='A' AND uniqueKeyTwo='B' AND uniqueKeyThree='C'");

if (empty($model)) {

    $model=new Model;

}

$model->uniqueKeyOne='A';

$model->uniqueKeyTwo='B';

$model->uniqueKeyThree='C';

$model->save();




But I don’t want to do that. It would be nice if I could do something like this:





$model=new Model;

$model->uniqueKeyOne='A';

$model->uniqueKeyTwo='B';

$model->uniqueKeyThree='C';

$model->replace();




Any ideas?

what exactly are you trying to accomplish here? create the model if it doesn’t exists, or update some fields if it does? what exactly would be the functionality of a “replace()” method?

When you use the save method, Yii creates an INSERT SQL statement:




INSERT INTO `tableName` (`uniqueKeyOne`,`uniqueKeyTwo`,`uniqueKeyThree`) VALUES ('A','B','C');



I want to use a REPLACE SQL statement:




REPLACE INTO `tableName` (`uniqueKeyOne`,`uniqueKeyTwo`,`uniqueKeyThree`) VALUES ('A','B','C');



I think you need a different approach.

A primary key identifies a record, so you can’t change that.

Why not delete the previous record and create a new one?

What you’re trying to do seems very unusual and flawed. IMO.

How about recreating the schema so that the model uses a normal primary key, and then make the unique_id a unique index?

That would enable you to have your cake and eat it too. :)

Trying to mock with a primary key will cascade all over your database, if it’s referenced in other records.

And, if you’re not using the proper foreign key constraints, will render a lot of data invalid.

So don’t do it.

Using a REPLACE statement does exactly that. It saves the DB call to see if a record exists first, it automatically deletes and inserts a new record if it does. http://dev.mysql.com/doc/refman/5.0/en/replace.html

The table I’m using does have a primary key, regardless of that these three fields should be a combined unique key.

The process I’m using this in can take as long as 20 minutes everytime it runs. Reducing queries speeds everything up.

Well, in that case: just run the query as-is. Have you tried that? :)

Didnt know about the existense of replace :D

I was hoping there was some way to do it with AR. Doesn’t seem so.

If ‘REPLACE’ is specific to MySql, it can’t be in AR ( in the schema) because it is not universal across backends.

If you want raw performance, using raw SQL is the best option.

That would save you the overhead of AR.

I second that, if what you need is performance then start by avoiding AR when possible.

Thanks for the tips.