All geek questions in one place

Why is Doctrine trying to duplicate many-to-many relationships, although I check to see if it exists in advance? And why does it accidentally crash into a flash?

Itโ€™s really hard for me when Doctrine is not working properly.

What my code is trying to do.

I am writing a CLI command in my Symfony 3 web application, which should clean up the tag table in my database. There are actors and tags. There is a many-to-many relationship between participants and tags (bidirectional). My team imports a CSV file in which tags are listed in one column and some replacements for them in another column. It goes through the file, takes turns, finds the existing tag, reads all current relationships with the Actors, removes the tag, creates a new tag (replaces) or uses the existing one, and attaches all the deleted relationships to it to it.

Code (its key part)

protected function doReplace(InputInterface $input, OutputInterface $output, $input_file)
{
    $em = $this->getContainer()->get('doctrine')->getManager();
    $con = $em->getConnection();

    //open the input CSV
    $input_fhndl = fopen($input_file, 'r');
    if (!$input_fhndl)
        throw new \Exception('Unable to open file!');

    //do everything in a big transaction, so that if anything fails
    //everything rolls back and there no half-finished information
    //in the DB
    $con->beginTransaction();
    try
    {
        //I was trying to use official Doctrine recommendation for batch inserts
        //to clear the entity manager after a bunch of operations, 
        //but it does neither help nor make things worse
//      $batchSize = 20;
        $i = 0;
        //reading the file line by line
        while (($line = fgetcsv($input_fhndl)) !== false) 
        {
            //$line[0] - source tag ID (the one to be substituted)
            //$line[1] - source tag type ('language' or 'skill')
            //$line[2] - source tag value (e.g. 'pole dancing (advanced)')
            //$line[3] - replacement tag value (e.g. 'pole dancing')

            $i++;

            if ($i === 1) //omit table headers
                continue;

            $line[3] = trim($line[3]);
            if ($line[3] === null || $line[3] === '') //omit lines with no replacements
                continue;

            //getting the tag to be replaced
            $src_tag = $em->getRepository('AppBundle:Tag')
                ->find($line[0]);
            if (!$src_tag)
            {
                //if the tag that is supposed to be replaced doesn't exist, just skip it
                continue;
            }


            $replacement_tag = null;
            $skip = false;

            //if the replacement value is '!' they just want to delete the original
            //tag without replacing it
            if (trim($line[3]) === '!')
            {
                $output->writeln('Removing '.$line[2].' ');
            }
            //here comes the proper replacement
            else
            {
                //there can be a few replacement values for one source tag
                //in such case they're separated with | in the input file
                $replacements = explode('|', $line[3]);

                foreach ($replacements as $replacement)
                {
                    $skip = false;
                    $output->write('Replacing '.$line[2].' with '.trim($replacement).'. ');

                    //getOrCreateTag looks for a tag with the same type and value as the replacement
                    //if it finds one, it retrieves the entity, if it doesn't it creates a new one
                    $replacement_tag = $this->getOrCreateTag($em, $src_tag->getTagType(), trim($replacement), $output);
                    if ($replacement_tag === $src_tag) //delete the original tag only if it is different from the replacement
                    {
                        $skip = true;
                    }
                    else
                    {
                        //we iterate through deleted Tag relationships with Actors
                        foreach ($src_tag->getActors() as $actor)
                        {
                            //this part used to be the many-to-many fail point but i managed to fix it by removing indexBy: id line from Actor->Tag relation definition
                            if (!$replacement_tag->getActors() || !$replacement_tag->getActors()->contains($actor))
                                $replacement_tag->addActor ($actor);
                        }
                        $em->persist($replacement_tag);
                        //...and if I uncomment this flush()
                        //I get errors like Notice: Undefined index: 000000005f12fa20000000000088a5f2
                        //from Doctrine internals
                        //even though it should be harmless
//                      $em->flush();
                    }
                }
            }

            if (!$skip) //delete the original tag only if it is different from the replacement
            {
                $em->remove($src_tag);
                $em->flush(); //this flush both deletes the original tag and sets up the new one
                //with its relations
            }

//          if (($i % $batchSize) === 0) {
//              $em->flush(); // Executes all updates.
//              $em->clear(); // Detaches all objects from Doctrine!
//          }
        }
        $em->flush(); //one final flush just in case
        $con->commit();
    }
    catch (\Exception $e)
    {
        $output->writeln('<error> Something went wrong! Rolling back... </error>');
        $con->rollback();
        throw $e;
    }

    //closing the input file
    fclose($input_fhndl);
}

protected function getOrCreateTag($em, $tag_type, $value, $output)
{
    $value = trim($value);

    $replacement_tag = $em
        ->createQuery('SELECT t FROM AppBundle:Tag t WHERE t.tagType = :tagType AND t.value = :value')
        ->setParameter('tagType', $tag_type)
        ->setParameter('value', $value)
        ->getOneOrNullResult();

    if (!$replacement_tag)
    {
        $replacement_tag = new Tag();
        $replacement_tag->setTagType($tag_type);
        $replacement_tag->setValue($value);
        $output->writeln('Creating new.');
    }
    else
    {
        $output->writeln('Using existing.');
    }

    return $replacement_tag;
}

How it fails

Even if I do this: $replacement_tag->getActors()->contains($actor)Doctrine tries to create a duplicate Actor-Tag relation:

[Doctrine\DBAL\Exception\UniqueConstraintViolationException]
An exception occurred while executing 'INSERT INTO actor_tags (actor_id, tag_id) VALUES (?, ?)' with params [280, 708]:
SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "actor_tags_pkey"
DETAIL:  Key (actor_id, tag_id)=(280, 708) already exists.

Strike>

I managed to fix this by removing indexBy: idActor-> Tag from the definition of the relation (it was random).

, , flush() ,

- , - :

  [Symfony\Component\Debug\Exception\ContextErrorException]
  Notice: Undefined index: 000000001091cbbe000000000b4818c6


Exception trace:
 () at /src/__sources/atm/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:2907
 Doctrine\ORM\UnitOfWork->getEntityIdentifier() at /src/__sources/atm/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Collection/ManyToManyPersister.php:543
 Doctrine\ORM\Persisters\Collection\ManyToManyPersister->collectJoinTableColumnParameters() at /src/__sources/atm/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Collection/ManyToManyPersister.php:473
 Doctrine\ORM\Persisters\Collection\ManyToManyPersister->getDeleteRowSQLParameters() at /src/__sources/atm/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Collection/ManyToManyPersister.php:77
 Doctrine\ORM\Persisters\Collection\ManyToManyPersister->update() at /src/__sources/atm/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:388
 Doctrine\ORM\UnitOfWork->commit() at /src/__sources/atm/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:359
 Doctrine\ORM\EntityManager->flush() at /src/__sources/atm/src/AppBundle/Command/AtmReplaceTagsCommand.php:176
 AppBundle\Command\AtmReplaceTagsCommand->doReplace() at /src/__sources/atm/src/AppBundle/Command/AtmReplaceTagsCommand.php:60
 AppBundle\Command\AtmReplaceTagsCommand->execute() at /src/__sources/atm/vendor/symfony/symfony/src/Symfony/Component/Console/Command/Command.php:262
 Symfony\Component\Console\Command\Command->run() at /src/__sources/atm/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:848
 Symfony\Component\Console\Application->doRunCommand() at /src/__sources/atm/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:190
 Symfony\Component\Console\Application->doRun() at /src/__sources/atm/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Console/Application.php:80
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /src/__sources/atm/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:121
 Symfony\Component\Console\Application->run() at /src/__sources/atm/bin/console:28

$em->clear() .

  • flush(), undefined.
  • ().
  • $em->clear() 20 - .

.

YAML Actor- > Tag ( Actor):

 manyToMany:
    tags:
      targetEntity: AppBundle\Entity\Tag
      inversedBy: actors
      #indexBy: id
#the above line caused the Many-To-Many duplicate problem - commenting it out fixed that part of the problem.
      joinTable:
        name: actor_tags
        joinColumns:
          actor_id:
            referencedColumnName: id
        inverseJoinColumns:
          tag_id:
            referencedColumnName: id

YAML Tag- > Actor ( Tag):

  manyToMany:
    actors:
      targetEntity: AppBundle\Entity\Actor
      mappedBy: tags

Tag::addActor()

public function addActor(\AppBundle\Entity\Actor $actor)
{
    $this->actor[] = $actor;
    $actor->addTag($this);

    return $this;
}

Actor::addTag()

public function addTag(\AppBundle\Entity\Tag $tag)
{
    $this->tags[] = $tag;

    $this->serializeTagIds();

    return $this;
}

, . .

+4

:

Source: https://habr.com/ru/post/1679710/

More articles:


All Articles