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.

Sivie

This link might be useful... yiiframework.com/doc/guide/1.1/en/database.ar – Chapell

Answer 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.

Sivie

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'. – Charbonnier

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

Answer 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.

Laic

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

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... – Laic

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

What @Scilicet said is correct. He is asking how to implement ON-DUPLICATE-UPDATE. – Volsci

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

Answer 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

Tautologize

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

Answer 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).

Hollington

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 #1160165 – Charbonnier

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

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. – Hollington

@Hollington 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! – Handbook

@Hollington 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. – Charbonnier

@Blowski - Point taken. Thanks for the feedback. – Hollington

Answer 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.

Charbonnier

Answer 6

you have to use try catch like that:

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

Motoneuron

Answer 7

Ooops, sorry.. this answer for yii2

If you dont use yii model, this function generates mysql syntax insert on duplicates key update

static function insertDuplicate($table, $columns, $duplicates, $values="",$ignores=false){
        $params=array();
        $names=array();
        $tipe="VALUES";
        $ignore=($ignores===true)?"IGNORE":"";
        $placeholders=array();
        if(is_array($columns)){
            if(!isset($columns[0])){
                foreach($columns as $name=>$value)
                {
                    $names[]=$name;
                    if($value instanceof CDbExpression)
                    {
                        $placeholders[] = $value->expression;
                        foreach($value->params as $n => $v)
                            $params[$n] = $v;
                    }
                    else
                    {
                        $placeholders[] = ':' . $name;
                        $params[':' . $name] = $value;
                    }
                }
            }else{
                $names=$columns;
            }
            $myColumn=implode(', ',$names);
            if($values!=""){
                $myValue=$values;
            }else{
                $myValue='('.implode(', ', $placeholders).')';
            }
        }else{
            $myColumn=$columns;
            $myValue=$values;
        }
        if($values!=""){
            if(substr(strtoupper($values),0,6)=="SELECT"){
                $tipe="";
            }
        }
        $d = array();
        if(is_array($duplicates)){
            if(!isset($duplicates[0])){
                foreach($duplicates as $duplicate=>$act)
                {
                    
                    if($act=="increase"){
                        $dup=$table.".".$duplicate . ' = '.$table.".".$duplicate.' + VALUES('.$duplicate.')';
                    }elseif($act=="decrease"){
                        $dup=$table.".".$duplicate . ' = '.$table.".".$duplicate.' - VALUES('.$duplicate.')';
                    }else{
                        $dup=$table.".".$duplicate . ' = VALUES('.$duplicate.')';
                    }
                    $d[] = $dup;
                }
            }else{
                foreach($duplicates as $duplicate){
                    $dup=$duplicate . ' = VALUES('.$duplicate.')';
                    $d[] = $dup;
                }
            }
            $myDuplicate= implode(', ', $d);
        }else{
            $myDuplicate=$duplicates;
        }
        
        
        $sql='INSERT '.$ignore.' INTO ' . $table
            . ' (' . $myColumn . ') '.$tipe.' '
            . $myValue . ' ON DUPLICATE KEY UPDATE ' .$myDuplicate;
        return Yii::$app->db->createCommand($sql)->bindValues($params)->execute();
    }

Place that function into someclass, and dont forget use

use yii\db\Command;

in that class

That function can insert on key update, update increment, update decrement, update multi from a value, and update from select

Usage :

//to update available=1 and price into 100
someclass::insertDuplicate(
'ck_space_calendar_cache',
['sapce_id'=>1,'day'=>'2022-09-01','available'=>1,'price'=>100],
['available','price']
);

//to update price increase by 100, (if price is decrease then change it to decrease)
someclass::insertDuplicate(
'ck_space_calendar_cache',
['sapce_id'=>1,'day'=>'2022-09-01','price'=>100],
['price'=>'increase']
);

//to update mass with a value
someclass::insertDuplicate(
'ck_space_calendar_cache',
['sapce_id','day','price'],
['price'],
'(1,'2022-09-01',100),(2,'2022-09-01',300),(3,'2022-09-01',100)'
);

//to update mass with select from another table
someclass::insertDuplicate(
'ck_space_calendar_cache',
['sapce_id','day','price'],
['price'],
'SELECT otherid as sapce_id, otherday as day, otherprice as price from other WHERE otherprice>100'
);

Noyes