Doctrine: ON DUPLICATE KEY UPDATE

Question

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

Answer 1

for Symfony 2 use raw sql:

$em->getConnection()->prepare("INSERT INTO table SET 
    some_fields = "some data", created_at = NOW() 
    ON DUPLICATE KEY UPDATE
    some_fields = "some data", updated_at = NOW()
")->execute();

Answer 2

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 3

You can't. It's not supported by Doctrine right now.

What you could do is to imitate what MySQL does by checking if the entity exists and update/create it accordingly:

$em = $this->getEntityManager();

// Prevent race conditions by putting this into a transaction.
$em->transactional(function($em) use ($content, $type) {
  // Use pessimistic write lock when selecting.
  $counter = $em->createQueryBuilder()
    ->select('MyBundle:MyCounter', 'c')
    ->where('c.content = :content', 'c.type = :type')
    ->setParameters(['content' => $content, 'type' => $type])
    ->setLockMode(\Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE);
    ->getQuery()
    ->getResult()
  ;

  // Update if existing.
  if ($counter) {
    $counter->increase();
  } else {
    // Create otherwise.
    $newCounter = new Counter($content, $type, 1);
    $em->persist($newCounter);
  }
});

If the record exists PESSIMISTIC_WRITE makes sure that it's not updated by anyone (e.g., other threads) while we're updating it.

Although you need to check for the entity's existence on every update, it's a simple reproduction of "update if existing and create if not".

As pointed out in the comments this does not prevent a race condition if the record doesn't exist: If a row with the same key(s) gets inserted between the select and the insert you're running into a duplicate key exception.

But given the constraints that this needs to be DB independent and thus written using Doctrine and not using native SQL it may help in some cases.

References:

• Database Administrators: Putting a Select statement in a transaction
• MySQL Reference Manual: Locking Reads (SELECT ... FOR UPDATE)
• Doctrine 2 ORM documentation: Transactions and Concurrency
• Stackoverflow: Doctrine2 ORM select for update

Answer 4

I had the same problem and after investigating a bit it looks like Doctrine doesn't do it. My solution was to do a findBy before my insert to see if any records exist with the unique fields. If this returns an entity then I update that entity and persist it instead of creating a new entity to persist.

If you are concerned about performance then this is not ideal as we are doing a select before every insert. However since Doctrine is database agnostic it is the only alternative to locking yourself to MySQL. It's one of those tradeoffs: do you want performance or portability.

Answer 5

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