Yii Framework Active Record INSERT … ON DUPLICATE KEY UPDATE

Recently I was programming in the Yii Framework and ran into a situation where I wanted to use INSERT … ON DUPLICATE KEY UPDATE in an Active Record. In my research, I discovered that this is specific to MySQL. In my case, this was still be a valuable requirement for my Web Application and was worth extending Yii to get working. My approach was as follows:

  • Extend CMysqlCommandBuilder:
    • Added updateAllAttributesOnDuplicate function: updates the record if a duplicate is found
    • Added updateNoAttributesOnDuplicate function: does not update the record if a duplicate is found (but does not throw an exception)
    • Overrode the function “createInsertCommand” to implement the onDuplicate logic
  • Extend CActiveRecord:
    • Overrode the function “getCommandBuilder” to use the new Command Builder
    • Added updateAllAttributesOnDuplicate and updateNoAttributesOnDuplicate functions to call into the command builder and change how INSERT … ON DUPLICATE KEY UPDATE works
  • Extend Active Record Class
    • Overrode the function “init” to set the proper INSERT … ON DUPLICATE KEY UPDATE action

Although INSERT … ON DUPLICATE KEY UPDATE will almost always be used for non-incrementing or composite Primary Keys, this code will also work for auto-incrementing Primary Keys. If an auto-increment key is found, the logic in “createInsertCommand” will always attempt to use MySQL’s “LAST_INSERT_ID(expr)” function to set the LAST_INSERT_ID to that of the updated record (described at http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html).

Here are the 2 class files you will need plus an example of how to extend your Active Record class:

  1. Place the EMysqlCommandBuilder File in {app-path}/protected/components/EMysqlCommandBuilder.php
<?
class EMysqlCommandBuilder extends CMysqlCommandBuilder
{
    
    private $_onDuplicate=false;
    
    /**
     * acts like a normal insert
     */
    public function nothingOnDuplicate(){
        $this->_onDuplicate=false;
    }
    
    /**
     * does not update the record if a duplicate is found
     */
    public function updateNoAttributesOnDuplicate(){
        $this->_onDuplicate='updateNoAttributes';
    }
    
    /**
     * updates the record if a duplicate is found
     */
    public function updateAllAttributesOnDuplicate(){
        $this->_onDuplicate='updateAllAttributes';
    }
    
    /**
     * return current state of ON DUPLICATE KEY UPDATE 
     */
    public function getInsertOnDuplicate(){
        return $this->_onDuplicate;
    }
    
    /**
     * Creates an INSERT command.
     * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
     * @param array $data data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored.
     * @return CDbCommand insert command
     */
    public function createInsertCommand($table,$data)
    {
        $this->ensureTable($table);
        $fields=array();
        $values=array();
        $placeholders=array();
        $i=0;
        foreach($data as $name=>$value)
        {
            if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull))
            {
                $fields[]=$column->rawName;
                if($value instanceof CDbExpression)
                {
                    $placeholders[]=$value->expression;
                    foreach($value->params as $n=>$v)
                        $values[$n]=$v;
                }
                else
                {
                    $placeholders[]=self::PARAM_PREFIX.$i;
                    $values[self::PARAM_PREFIX.$i]=$column->typecast($value);
                    $i++;
                }
            }
        }
        if($fields===array())
        {
            $pks=is_array($table->primaryKey) ? $table->primaryKey : array($table->primaryKey);
            foreach($pks as $pk)
            {
                $fields[]=$table->getColumn($pk)->rawName;
                $placeholders[]='NULL';
            }
        }
        $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')';
        
        //begin onDuplicate logic
        if ($this->_onDuplicate!=false){
            $autoIncrement=false;
            $updateFields=array();
            if ($this->_onDuplicate=='updateAllAttributes'){
                $updateFields=$fields;
            }
            foreach($table->columns as $columnSchema){
                if ($columnSchema->autoIncrement){
                    $autoIncrement=$columnSchema->rawName;
                    break;
                }
            }
            if ($autoIncrement!==false || (!empty($updateFields) || $this->_onDuplicate=='updateNoAttributes')){
                $sql.=" ON DUPLICATE KEY UPDATE";
                
                if ($autoIncrement!==false){
                    if (($autoIncrementIndex=array_search($autoIncrement,$updateFields))!==false){
                        unset($updateFields[$autoIncrementIndex]);
                        $updateFields=array_values($updateFields);
                    }
                    $sql.=" {$autoIncrement}=LAST_INSERT_ID({$autoIncrement})";
                }
                
                if ($this->_onDuplicate=='updateAllAttributes'){
                    if (!empty($updateFields)){
                        if ($autoIncrement!==false){
                            $sql.=',';
                        }
                        $updateFieldsSql=array();
                        foreach($updateFields as $updateField){
                            $updateFieldsSql[]=" {$updateField}=VALUES({$updateField})";
                        }
                        $sql.=implode(',',$updateFieldsSql);
                    }
                } else if ($this->_onDuplicate=='updateNoAttributes' && $autoIncrement===false){
                    $firstColumn=reset($table->columns);
                    $sql.=" {$firstColumn->rawName}={$firstColumn->rawName}";
                }
            }
        }
        //end onDuplicate logic
        
        $command=$this->getDbConnection()->createCommand($sql);

        foreach($values as $name=>$value)
            $command->bindValue($name,$value);

        return $command;
    }
}

  1. Place the EActiveRecord File in {app-path}/protected/components/EActiveRecord.php
<?
abstract class EActiveRecord extends CActiveRecord
{
    
    private $_builder;
    
    public function getCommandBuilder(){
        if($this->_builder!==null)
            return $this->_builder;
        else
            return $this->_builder=new EMysqlCommandBuilder($this->getDbConnection()->getSchema());
    }
    
    public function nothingOnDuplicate(){
        $this->getCommandBuilder()->nothingOnDuplicate();
    }
    
    public function updateNoAttributesOnDuplicate(){
        $this->getCommandBuilder()->updateNoAttributesOnDuplicate();
    }
    
    public function updateAllAttributesOnDuplicate(){
        $this->getCommandBuilder()->updateAllAttributesOnDuplicate();
    }
    
}
  1. Extend EActiveRecord with your Active Record class and initialize according to how you want the INSERT … ON DUPLICATE KEY UPDATE to work for that Active Record.
<?
class MyActiveRecord extends EActiveRecord
{
    
    public function init(){
        $this->updateAllAttributesOnDuplicate();
        return parent::init();
    }

    //The rest of your normal active record class goes below here...

    /**
     * Returns the static model of the specified AR class.
     * @param string $className active record class name.
     * @return EmName the static model class
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    //etc.

}