SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '60' for key 'P-CodePudding

I encounter a problem, indeed, I want a function facing an update or an insert according to the existence or not.

I read in the Symfony documentation this: Whether you're creating or updating objects, the workflow is always the same: Doctrine is smart enough to know if it should INSERT or UPDATE your entity.

My repository :

public function refreshAllArticle()
{
    $articlesActualise = $this->ApiManager->getAllArticles();

    $entityManager = $this->getEntityManager();

    foreach ($articlesActualise as $articleActualise) {


        $article = new Articles();

        $article->setId($articleActualise['id']);

        $article->setDateCreation($articleActualise['date_creation']);
        $article->setDateModification($articleActualise['date_modification']);
        $article->setTitre($articleActualise['titre']);
        $article->setContent($articleActualise['content']);
        $article->setDescription($articleActualise['description']);

        $entityManager->persist($article);

        $entityManager->flush();
    }
}

My entity :

#[ORM\Entity(repositoryClass: ArticlesRepository::class)]
class Articles
{
    #[ORM\Id]
    #[ORM\Column(type: 'integer')]
    private $id;

    #[ORM\Column(type: 'date')]
    private $date_creation;

    #[ORM\Column(type: 'date', nullable: true)]
    private $date_modification;

    #[ORM\Column(type: 'string', length: 255)]
    private $titre;

    #[ORM\Column(type: 'text', nullable: true)]
    private $content;

    #[ORM\Column(type: 'text', nullable: true)]
    private $description;

    #[ORM\Column(type: 'text', nullable: true)]
    private $header;

    #[ORM\Column(type: 'text', nullable: true)]
    private $script;

And my controller :

#[Route('/refreshArticles', name: 'refreshArticles')]
public function refreshArticle(): RedirectResponse
{
    $this->doctrine->getRepository(Articles::class)->refreshAllArticle();
    return $this->redirectToRoute('articles');

}

The insertion works perfectly, but if I want to call this function again to make an update I get this error: An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '60' for key 'PRIMARY'

PS : For information, my ID is not in auto-increment because I want to be able to insert my own values

Could you help me thanks :)

CodePudding user response：

Doctrine does not know out of the box whether an entity with a given idenfitier already exists or not. It can determine what it should do with an entity (e.g. if you persist twice in the same execution it will only insert once, if you fetch an entity, update and persist it it will update the entity).

However you're currently always creating a new entity from the existing data and persisting it. Instead you should first check whether or not an entity with the given id already exists. I am assuming that this repository class extends the Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository class which provides a "find" function to get an entity by id) so here is an example:

public function refreshAllArticle()
{
    $articlesActualise = $this->ApiManager->getAllArticles();

    $entityManager = $this->getEntityManager();

    foreach ($articlesActualise as $articleActualise) {
        // Fetch existing article object
        $articleToUpdate = $this->find($articleActualise['id']);
        if ($articleToUpdate === null) {
            // Article does not exist so we need to create a new object
            $articleToUpdate = new Articles();
        }

        // Update logic goes here using setters on $articleToUpdate 
        // Just like you previously already did

        $entityManager->persist($articleActualise);

        $entityManager->flush();
    }
}

This way you will first check if the entity for the id already exists and update that one instead of creating a new one.