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;
	}
}

2. 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();
	}
	
}

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

}