insert ignore on duplicate entries in Doctrine2/Symfony2

Question

How to ignore on duplicate entries using Doctrine2?

Error example:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'symfony' for key 'UNIQ_389B783389B783'

Answer 1

That's one of the nuisances of Doctrine, it's not possible to do INSERT/UPDATE Ignore, there are workaround like creating a methods that checks if the row exists, and if it does then just skip it.

You can catch the exception, so that your script doesn't end in an exception. However, the entity manager will be closed and you will not be able to use it anymore. You can still use PDO, though and you can insert a record in the database indicating that your batch failed because X and it needs to be restarted (that's what I usually do).

If none of the options above work for you, ultimately I end up writing raw SQL to do batch processing and I don't use Doctrine at all, it ends up being faster and the ability of doing INSERT/UPDATE Ignore makes it a no brainer.

Answer 2

In Symfony 3 you can reset manager and continue working with it by calling resetManager() method of Doctrine object after catching UniqueConstraintViolationException exception.

Here is an example:

try {
  $em = $this->getDoctrine()->getManager();
  $entity = Product::create()
    ->setTitle('Some title')
    ->setUrl('http://google.com');
  $em->persist($entity);
  $em->flush();
}
catch (UniqueConstraintViolationException $e) {
  $this->getDoctrine()->resetManager();
}

Answer 3

You can always catch the exception and then ignore it. Just be aware that when the entity manager raises an exception, the entity manager can no longer be used during that request.

Answer 4

This problem answer. Symfony >=4.0 and Doctrine >= 2.0

My Entity Uniq Fields phone and channel

Import this class on your entity class

use Doctrine\Persistence\NotifyPropertyChanged;
use Doctrine\Persistence\PropertyChangedListener;

After implements NotifyPropertyChanged class

class TblPersonPhonePermission implements NotifyPropertyChanged

After addPropertyChangedListener methods

public function addPropertyChangedListener(PropertyChangedListener $listener)
{
    $insertBatch = $listener->getScheduledEntityInsertions();
    foreach ($insertBatch as $entity) {
        if (
            (get_class($entity) === get_class($this)) &&
            $this->getId() !== $entity->getId() &&
            $this->getPhone() === $entity->getPhone() &&
            $this->getChannel() === $entity->getChannel()
        ) {
            $listener->remove($this);
        }
    }
}

Answer 5

You can also check for duplicates before actually inserting. I had a similar issue and answered it here: doctrine/symfony 4 - avoid duplicate entry when persisting child entities

protected function removeDuplicates($insertions) {
    foreach ($insertions as $key => $insertion) {
        $shortClassName = (new ReflectionClass($insertion))->getShortName();
        // TODO: The search can be heavily optimized
        foreach ($insertions as $possibleDupeKey => $possibleDupeInsertion) {
            $shortDupeClassName = (new ReflectionClass($insertion))->getShortName();
            // TODO: Treat case when unique is on a field not called 'id'
            if ($shortClassName === $shortDupeClassName && $insertion->getId() === $possibleDupeInsertion->getId() && $key !== $possibleDupeKey) {
                $this->em->remove($possibleDupeInsertion);
            }
        }
    }
}

protected function saveStuff($order) {
    $this->em->persist($order);
    $this->removeDuplicates($this->em->getUnitOfWork()->getScheduledEntityInsertions());
    $this->em->flush();
}