Doctrine ORM self ManyToOne fails to insert because of duplicate entries

Question

Problem

Is there a way that Doctrine recognizes the existing objects when persist with cascade={"persist"} on a ManyToOne relation instead and do not fail when trying to insert it again and thus violate the unique key rule?

Description

I am trying to create a Location Entity that can reference a parent to obtain this kind of structure :

To do this, I have the following code on my entity :

/**
 * Abstract class representing a location
 *
 * @ORM\Entity
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="type", type="string")
 * @ORM\DiscriminatorMap({"COUNTRY" = "CountryLocation", "REGION" = "RegionLocation", "DEPARTMENT" = "DepartmentLocation"})
 * @ORM\Table(name="ss_locations")
 *
 * @package Locations
 */
abstract class ALocation {
  
  /**
   * A type that determines the location type
   */
  protected ?string $type = null;
  
  /**
   * The location ID
   *
   * @ORM\Id
   * @ORM\GeneratedValue
   * @ORM\Column(type="integer", options={"unsigned"=true})
   *
   * @var int
   */
  protected int $id;
  
  /**
   * The location's slug identifier
   *
   * @ORM\Column(type="string")
   *
   * @example "Pays de la Loire" region's slug will be "pays-de-la-loire"
   *
   * @var string
   */
  protected string $slug;
  
  /**
   * The location path through its parent's slugs
   *
   * @ORM\Column(type="string", unique=true)
   *
   * @example "Loire-Atlantique" department's path would be "france/pays-de-la-loire/loire-atlantique"
   *
   * @var string
   */
  protected string $path;
  
  /**
   * The name location's
   *
   * @ORM\Column(type="string")
   *
   * @var string
   */
  protected string $name;
  
  /**
   * The parent location instance
   *
   * @ORM\ManyToOne(targetEntity="ALocation", cascade={"persist"})
   * @ORM\JoinColumn(name="parent", referencedColumnName="id")
   *
   * @var ALocation|null
   */
  protected ?ALocation $parent = null;

  // ...

}

// Example of child class 

/**
 * Class DepartmentLocation
 *
 * @ORM\Entity
 *
 * @package Locations
 */
class DepartmentLocation extends ALocation {
  const TYPE = "DEPARTMENT";
  
  /**
   * @inheritdoc
   */
  protected ?string $type = "DEPARTMENT";

  // ...
}

The table creation goes well, but when I try to persist one location I got these errors :

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'FR' for key 'PRIMARY')
Warning: Île-de-France cannot be inserted in DB : reason(An exception occurred while executing 'INSERT INTO ss_locations (iso_code, name, parent_id, type) VALUES (?, ?, ?, ?)' with params ["FR", "France", null, "COUNTRY"]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'FR' for key 'PRIMARY')
Warning: Paris cannot be inserted in DB : reason(An exception occurred while executing 'INSERT INTO ss_locations (iso_code, name, parent_id, type) VALUES (?, ?, ?, ?)' with params ["FR", "France", null, "COUNTRY"]:

Here is an example of the Database content wanted

And here is how I try to persist it :

// ...

  foreach ( $locations as $location ) :
    try {
      DoctrineEntityManager::get()->persist($location);
      DoctrineEntityManager::get()->flush();
    } catch ( Throwable $e ) {}
  }

// ...

Answer 1

After searches, I managed to use EntityManager::merge( $entity ); followed by a EntityManager::flush(); call to get it work, just like described here https://stackoverflow.com/a/46689619/8027308.

// ...

foreach ( $locations as $location ) :
  // Persist the main location
  try {
    DoctrineEntityManager::get()->merge($location);
    DoctrineEntityManager::get()->flush();
  } catch ( Throwable $e ) {}
endforeach;

// ...

However, the EntityManager::merge( $entity ) is being marked deprecated and will be removed from Doctrine 3+. There is no official alternative yet and it probably won't be.

Workarounds

1. EntityManager::getUnitOfWork()::registerManaged()

I tried the alternative proposed here https://stackoverflow.com/a/65050577/8027308, but using EntityManager::getUnitOfWork()::registerManaged() didn't work in my case and caused the same error as before SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'FR' for key 'PRIMARY'). Moreover, this alternative required one or two more dependencies to convert entity data into array. Here is the code that produced the error :

use Symfony\Component\Serializer\Serializer;
use Symfony\Component\Serializer\Normalizer;

// ...  

$serializer = (new Serializer([new Normalizer\ObjectNormalizer()], []));
foreach ( $locations as $location ) :
  // Persist the main location
  try {
    DoctrineEntityManager::get()->getUnitOfWork()->registerManaged(
      $location, // The entity object
      [ $location->getIsoCode() ], // The entity identifiers
      $serializer->normalize($location, null) // Gets the entity data as array
    );
    DoctrineEntityManager::get()->flush();
  } catch ( Throwable $e ) {}
endforeach;

// ...

2. Let Doctrine do a pre-query to check if the parent entity exists

Otherwise, you can disable cascade={"persist"} on your entity property and do the cascade by yourself, using a Doctrine to perform a pre-query to check whether or not the entity already exists in DB or not :

// ...

/**
 * Recursively save all the parents into the DB
 * 
 * @param ALocation $location The location to save the parents from
 *                            
 * @return void
 */  
function persistParents( ALocation $location ) : void {
  // If we don't have any parent, no need to go further
  if ( ! $location->hasParent() )
    return;
  
  $parent = $location->getParent();
  
  // Recursion to save all parents
  if ($parent->hasParent())
    persistParents($parent);
  
  // Try to get the parent from the DB
  $parentRecord = DoctrineEntityManager::get()->getRepository( ALocation::class )->find( $parent->getIsoCode() );
  
  // If we succeed, we set the parent on the location and exit
  if ( ! is_null($parentRecord) ) {
    $location->setParent( $parentRecord );
  
    return;
  }
  
  // Otherwise, we save it into the DB 
  try {
    DoctrineEntityManager::get()->persist( $parent );
    DoctrineEntityManager::get()->flush();
  } catch (Throwable $e) {}
  
  return;
}

foreach ( $locations as $location ) :
  // Saves all the parents first
  if ($location->hasParent())
    persistParents( $location );
  
  // Then persist the main location
  try {
    DoctrineEntityManager::get()->persist($location);
    DoctrineEntityManager::get()->flush();
  } catch ( Throwable $e ) {}
endforeach;

// ...

3. Use good old INSERT ... ON DUPLICATE KEY UPDATE

Previous workarounds want only to use Doctrine, but a safer and cleaner solution will be to use native SQL query such as described here: https://stackoverflow.com/a/4205207/8027308

Answer 2

The problem is not your Doctrine configuration but how you create your object, if you look at those errors messages you'll find that Doctrine try to insert the same data for France and Paris, try to add $type property without doctrine mapping to

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'FR' for key 'PRIMARY')
Warning: Île-de-France cannot be inserted in DB : reason(An exception occurred while executing 'INSERT INTO ss_locations (iso_code, name, parent_id, type) VALUES (?, ?, ?, ?)' with params ["FR", "France", null, "COUNTRY"]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'FR' for key 'PRIMARY')
Warning: Paris cannot be inserted in DB : reason(An exception occurred while executing 'INSERT INTO ss_locations (iso_code, name, parent_id, type) VALUES (?, ?, ?, ?)' with params ["FR", "France", null, "COUNTRY"]:

Update

As discussed in replies, the main problem came from the way of constructing entities, the solution was to create unique objects for parent entities then attach them to all children (working with references)

$france = new CountryLocation('France');
$iledefrance = new DepartmentLocation('Ile-de-france');
$iledefrance->setParent($france);
$paris = new CityLocation('Paris');
$paris->setParent($iledefrance);
....