ManyToOne - why it create duplicate foreign object / Traget Entity

Question

This is my database, as you can see, I have two Many To One relation and the owner of relationship is the table ESA. For this web app, I use Symfony 4 and doctrine as ORM, and MySQL 5.7.24, PHP 7.2 .

(https://i.sstatic.net/i5uHt.jpg)

The process is :

1. I upload as csv
2. Create a row in database table filesupload with mention of this csv
3. Read the csv
4. Import each row of csv into the ESA table and set id_filesupload field with the filesupload object

I try to use the :

$entityManager->merge(); Working greate but only when id_filesupload has been already set in the table ESA.

It's doesn't create duplicate filesupload row with same value.

It duplicate my filesupload everytime I flush. I have try to no pass the object filesupload to the import function but only the id, and get the object by the id.. the result is the same.

In a other hand, the exactly the same process for Department and it doesn't create duplicate entries into Department table.

Part of my Entity ESA

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Esa
 *
 * @ORM\Table(name="esa")
 * @ORM\Entity(repositoryClass="App\Repository\EsaRepository")
 */
class Esa
{     

     /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Department", inversedBy="Esa")
     * @ORM\JoinColumn(name="department_id", referencedColumnName="id", nullable=true)
     */
    private $department;



    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Filesupload", inversedBy="Esa")
     * @ORM\JoinColumn(name="filesupload_id", referencedColumnName="id", nullable=true)
     */
    private $filesupload;


    public function getDepartment(): ?Department
    {
        return $this->department;
    }

    public function setDepartment(?Department $department): self
    {
        $this->department = $department;

        return $this;
    }

    public function getFilesupload(): ?Filesupload
    {
        return $this->filesupload;
    }

    public function setFilesupload(?Filesupload $filesupload): self
    {
        $this->filesupload = $filesupload;

        return $this;
    }


}

Part of my Controller ESA Upload the CSV (Process step 1 + 2)

/**
 * @Route("/Aqueduct/UploadData", name="Aqueduct_Upload")
 */
public function UploadData(Request $request)
{
    $entityManager = $this->getDoctrine()->getManager();
    $form = $this->createForm(FilesuploadType::class);
    $form->handleRequest($request);
    if ($form->isSubmitted() && $form->isValid()) {
        $FilesuploadFile = $form['UploaderESA']->getData();
        // this condition is needed because the 'ESA csv' field is not required
        // so the CSV file must be processed only when a file is uploaded
        if ($FilesuploadFile) {
            $originalFilename = pathinfo($FilesuploadFile->getClientOriginalName(), PATHINFO_FILENAME);
            // this is needed to safely include the file name as part of the URL
            $safeFilename = transliterator_transliterate('Any-Latin; Latin-ASCII; [^A-Za-z0-9_] remove; Lower()', $originalFilename);
            //$newFilename = $safeFilename.'-'.uniqid().'.'.$brochureFile->guessExtension();
            //force csv
            $newFilename = $safeFilename.'-'.uniqid().'.csv';

            // Move the file to the directory where csv are stored
            try {
                //get the new param of moving file
                $FilesuploadFile=$FilesuploadFile->move(
                    $this->getParameter('uploads_directory'),
                    $newFilename
                );
                // create and set this Fileupload
                $FileUpload = new Filesupload();
                $FileUpload
                    ->setType("ESA")
                    ->setFilename($newFilename);
                // save the uploaded filename to database
                $entityManager->persist($FileUpload);
                $entityManager->flush();
                $entityManager->clear();
            } catch (FileException $e) {
                // ... handle exception if something happens during file upload
            }
        }
        $this->ImportESA($FilesuploadFile, $FileUpload);
    }
    else{
    return $this->render('Aqueduct/import.html.twig', [ 'form' => $form->createView()]);
    }
}

Part of my Controller ESA Upload the CSV (Process step 3 + 4)

public function ImportESA($FilesuploadFile, $FileUpload)
    {

        $batchSize = 80;
        $i=0;
        $entityManager = $this->getDoctrine()->getManager();
        $repositoryESA = $this->getDoctrine()->getRepository(Esa::class);



        $csv = Reader::createFromPath($FilesuploadFile->getRealPath(), 'r');
        //param the header of the array
        $csv->setDelimiter(';');
        $csv->setEnclosure('"');
        $csv->setHeaderOffset(0);
        /*$csv->setEncodingFrom('iso-8859-15');*/
        $records = $csv->getRecords();

        foreach ($records as $offset => $record) {

                //Remove matu and degree and class split
                $classLetter = $this->RemoveMatuTag($this->AllLettersBeforeNumb($record["Classe - Nom"]));
                $department = $this->GetDepartmentByClasseName($classLetter);
                    ++$i;
                    $EsaRecord = new Esa();
                    $EsaRecord
                        ->setDepartment($department)
                        ->setConcatenate($Concatenate)
                        ->setFilesupload($FileUpload)
                        ;

                        $entityManager->persist($EsaRecord);

                    if (($i % $batchSize) === 0) {
                        $entityManager->flush();
                        $message = 'Done';
                        $entityManager->clear(); // Detaches all objects from Doctrine!

                    }
                }
            }
        }
        $entityManager->flush();
        $entityManager->clear(); // Detaches all objects from Doctrine!
        return $this->redirect($this->generateUrl('Aqueduct_TransformData'));
    }

How I get the department

public function AllLettersBeforeNumb($var)
{
    return preg_replace("/\d.*/","$2",$var);
}
public function RemoveMatuTag($var)
{
    return str_replace(" MATU", "",$var);
}
public function GetDepartmentByClasseName($var)
{
    $repository = $this->getDoctrine()->getRepository(Education::class);
    $education = $repository->findOneBy(['Shorten' => $var]);
    $department = NULL;
    if ($education != NULL) {
        $department = $education->getDepartment();
    }
    if (! $department){
        $repository = $this->getDoctrine()->getRepository(Department::class);
        $department = $repository->find(0);
    }
    return $department;
}

As my understanding I don't want to : cascade={"persist"} cause it will create an filesupload row for each row in my CSV (ESA table).

I expect the have only 1 filesupload row for all my new esa row. But actual I have 1 filesupload for each packet of 80 lines, cause of $entityManager->flush();

I have 17160 row in my csv.

Answer 1

It's cause I unset the $FileUpload when I call the clear() method of the entity manager. My bad...