Doctrine: ON DUPLICATE KEY UPDATE

Question

How can I write an INSERT doctrine query with option ON DUPLICATE KEY UPDATE?

Answer 1

The problem is that this is a MySQL specific problem so it will not be directly covered by Doctrine.

As a comment mentioned, you would need to write a RawSQL Query for this. This would be the easiest way.

If you want it more sophisticated and truely DB independent, look into Events and it's possibilities. Before the actual query is executed, you can check for an existence and if it exists, act accordingly.

An ORM/PHP independent way is to write a stored procedure/trigger that handles this problem database side.

Answer 2

In case this helps, you can extend the query builder to append arbitrary SQL (obviously, this may not work across PDO engines):

class MyQB extends QueryBuilder {

    private $append = '';

    /**
     * {@inheritdoc}
     */
    public function getSQL() {
        return parent::getSQL() . $this->append;
    }

    /**
     * Append raw SQL to the output query
     *
     * @param string $sql SQL to append. E.g. "ON DUPLICATE ..."
     *
     * @return self
     */
    public function appendSql($sql) {
        $this->append = $sql;
        return $this;
    }
}

Answer 3

I created a doctrine dbal wrapper to do that. It can be used with DoctrineBundle with the dbal wrapper_class option.

https://github.com/iJanki/doctrine-mysql-dbal-extensions

Answer 4

I wrote simple solution for me. Just created AbstractRepository class which is parent class of all Repositories(for example UserRepository) and created next method:

 public function onDuplicateUpdate($insertFields, $updateFields)
    {
        $table = $this->getEntityManager()->getClassMetadata($this->getEntityName())->getTableName();
        $sql = 'INSERT INTO '.$table;
        $sql .= '(`'.implode('`,`', array_flip($insertFields)).'`) ';
        $sql .= 'VALUES("'.implode('","', $insertFields).'") ';
        $sql .= 'ON DUPLICATE KEY UPDATE ';
        foreach($updateFields as $column => $value) {
            $sql .= '`'.$column . '` = "'. $value.'"';
        }
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute();
    }

You can use this code like this:

$this->getEntityManager()
           ->getRepository('User')
           ->onDuplicateUpdate(['column1' => 'user_reminder_1', 'column2' => 235], ['column2' => 255]);

Answer 5

You have three options.

The first option is to drop down to SQL. Then you can use all the features your RDBMS of choice provides. But many programmers don't want to drop down to SQL unless absolutely necessary.

The second option is to lock on a related row in another table. For instance, if the entity you're inserting has a unique key per user, you could do a lock on the user you're inserting/updating the entity for. The problem with this solution is that it doesn't work for root entities like User itself because you can't lock a row that doesn't exist yet.

The third option is to just catch the duplicate key error/exception. That is, you don't check if a row with a particular key already exists; instead, you just attempt to insert it. If it succeeds, all is good. If it fails with the duplicate key error/exception, you catch it and update the existing row. This solution is the best because it avoids an extra SELECT query before each insertion that's a constant overhead for the low probability of hitting a race condition. And it's the best because it works for both root and non-root entities.

Answer 6

You can use function like this to build and execute raw sql:

 /**
 * 
 * insertWithDuplicate('table_name', array('unique_field_name' => 'field_value', 'field_name' => 'field_value'), array('field_name' => 'field_value'))
 * 
 * @param string $tableName
 * @param array $insertData 
 * @param array $updateData
 * 
 * @return bolean
 */
public function insertWithDuplicate($tableName, $insertData, $updateData) {
    $columnPart = '';
    $valuePart = '';
    $columnAndValue = '';
    foreach ($insertData as $key => $value) {
        $value = str_replace(array('"', "'"), array('\"', "\'"), $value);
        $columnPart .= "`" . $key . "`" . ',';
        is_numeric($value) ? $valuePart .= $value . ',' : $valuePart .= "'" . $value . "'" . ',';
    }
    foreach ($updateData as $key => $value) {
        $value = str_replace(array('"', "'"), array('\"', "\'"), $value);
        is_numeric($value) ? $columnAndValue .= $key . ' = ' . $value . ',' : $columnAndValue .= "`" . $key . "`" . ' = ' . "'" . $value . "'" . ',';
    }
    $_columnPart = substr($columnPart, 0, strlen($columnPart) - 1);
    $_valuePart = substr($valuePart, 0, strlen($valuePart) - 1);
    $_columnAndValue = substr($columnAndValue, 0, strlen($columnAndValue) - 1);
    $query = "INSERT INTO " . $tableName .
            " (" . $_columnPart . ") "
            . "VALUES" .
            " (" . $_valuePart . ") "
            . "ON DUPLICATE KEY UPDATE " .
            $_columnAndValue;
    return $this->entityManager->getConnection()
                    ->prepare($query)->execute();
}