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.

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

you have to use try catch like that:

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

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'
);