Doctrine ORM: Persisting collections with Composite Primary Keys composed of Foreign Keys

Question

I'm guessing this is a Doctrine bug (and I've submitted an issue on JIRA issue tracker), but in case it's just user error I decided to post it here.

SYNOPSIS

Persisting a collection of entities in a join table with a Composite Primary Key made up of 2 Foreign Keys and one metadata field fails in certain situations. Code was based on instructions here: Doctrine docs

ISSUE DETAILS

SUCCESS: When FOREIGN KEY 1 is the same across items in a collection to be persisted, and FOREIGN KEY 2 is greater than FOREIGN KEY 2 in any existing PRIMARY KEY, the entity and related entities in the collection are persisted correctly:

Example: GPA "add val below" exists and has assessment value {"assessment":6,"value":4} We will try to add a new assessment value where assessment_id > that of any existing assessment value for GPA "add val below"
Request Payload: {"name":"add val below","courses":[],"assessmentValues":[{"assessment":6,"value":4},{"assessment":7,"value":3}]}

Debug log:

[2013-12-31 11:48:48] app.INFO: GPA ID PRESAVE IN CONTROLLER:9 [] []
[2013-12-31 11:48:48] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL ASSESSMENT ID:7 [] []
[2013-12-31 11:48:48] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL POINTS:3 [] []
[2013-12-31 11:48:48] app.INFO: GPA ID PRESAVE IN CONTROLLER:9 [] []
[2013-12-31 11:48:48] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL ASSESSMENT ID:6 [] []
[2013-12-31 11:48:48] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL POINTS:4 [] []
[2013-12-31 11:48:48] doctrine.DEBUG: "START TRANSACTION" [] []
[2013-12-31 11:48:48] doctrine.DEBUG: INSERT INTO gpa_assessment_value (point_value, grade_point_average_id, assessment_id) VALUES (?, ?, ?) {"1":3,"2":"9","3":"7"} []
[2013-12-31 11:48:48] doctrine.DEBUG: UPDATE gpa_assessment_value SET point_value = ? WHERE grade_point_average_id = ? AND assessment_id = ? [4,9,6] []
[2013-12-31 11:48:48] doctrine.DEBUG: "COMMIT" [] []

FAILURE: When FOREIGN KEY 1 is the same across items in a collection, and FOREIGN KEY 2 is less than any existing FOREIGN KEY 2, the unit of work tries to INSERT existing entity and does not operate on new entity.

Example: GPA "add val above" exists and has assessment value {"assessment":8,"value":2} We will try to add a new assessment value where assessment_id < that of any existing assessment value for GPA "add val above"
Request Payload: {"name":"add val above","courses":[],"assessmentValues":[{"assessment":6,"value":4},{"assessment":8,"value":2}]}

Debug log:

[2013-12-31 11:53:59] app.INFO: GPA ID PRESAVE IN CONTROLLER:10 [] []
[2013-12-31 11:53:59] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL ASSESSMENT ID:8 [] []
[2013-12-31 11:53:59] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL POINTS:2 [] []
[2013-12-31 11:53:59] app.INFO: GPA ID PRESAVE IN CONTROLLER:10 [] []
[2013-12-31 11:53:59] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL ASSESSMENT ID:6 [] []
[2013-12-31 11:53:59] app.INFO: PRESAVE IN CONTROLLER ASSESSMENT VAL POINTS:4 [] []
[2013-12-31 11:53:59] doctrine.DEBUG: "START TRANSACTION" [] []
[2013-12-31 11:53:59] doctrine.DEBUG: INSERT INTO gpa_assessment_value (point_value, grade_point_average_id, assessment_id) VALUES (?, ?, ?) {"1":2,"2":"10","3":"8"} []
[2013-12-31 11:53:59] doctrine.DEBUG: "ROLLBACK" [] []
[2013-12-31 11:53:59] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\DBALException: "An exception occurred while executing 'INSERT INTO gpa_assessment_value (point_value, grade_point_average_id, assessment_id) VALUES (?, ?, ?)' with params [2, "10", "8"]:
SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "gpa_assessment_value_pkey"

CODE

migration.sql

CREATE TABLE assessment
(
    id       bigserial NOT NULL,
    scale_id bigint    NOT NULL,
    title    varchar   NOT NULL,
    passing  boolean   NOT NULL,
    rank     int,

    PRIMARY KEY (id)
);

CREATE TABLE assessment_scale
(
    id   bigserial NOT NULL,
    name varchar   NOT NULL,

    PRIMARY KEY (id)
);
-- ...

CREATE TABLE grade_point_average
(
    id                         bigserial       NOT NULL,
    name                       varchar         NOT NULL,
    additional_credit_allowance numeric(4, 2),

    PRIMARY KEY (id)
);

-- ...

CREATE TABLE gpa_assessment_value
(
    grade_point_average_id bigint        NOT NULL,
    assessment_id          bigint        NOT NULL,
    point_value            numeric(4, 2) NOT NULL,

    PRIMARY KEY (assessment_id, grade_point_average_id),
    FOREIGN KEY (assessment_id) REFERENCES assessment,
    FOREIGN KEY (grade_point_average_id) REFERENCES grade_point_average
);

Model/GradePointAverage.php

namespace MyApp\Model;

use Doctrine\ORM\Mapping\Entity;
use Doctrine\ORM\Mapping\Id;
use Doctrine\ORM\Mapping\GeneratedValue;
use Doctrine\ORM\Mapping\Column;
//...
use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\ArrayCollection;
use MyApp\Util\ConstructorArgs;
use MyApp\Model\GradePointAverage\AssessmentValue;
// ...

/**
 * @Entity("MyApp\Repository\GradePointAverageRepository")
 */
class GradePointAverage
{
    use ConstructorArgs;

    /**
     * @Id
     * @GeneratedValue
     * @Column(type="bigint")
     *
     * @var int
     */
    private $id;

    // ...

    /**
     * @OneToMany(targetEntity="MyApp\Model\GradePointAverage\AssessmentValue", mappedBy="gradePointAverage", cascade="persist")
     *
     * @var Collection
     */
    private $assessmentValues;

    // ...

    /**
     * @param array $args
     */
    public function __construct(array $args = [])
    {
        $this->assessmentValues = new ArrayCollection;
        // ...
        $this->handleArgs($args);
    }

    // ...

    /**
     * @return Collection
     */
    public function getAssessmentValues()
    {
        return $this->assessmentValues;
    }

    /**
     * @param ArrayCollection $assessmentValues
     */
    public function setAssessmentValues(ArrayCollection $assessmentValues)
    {
        $this->assessmentValues = $assessmentValues;
    }

    /**
     * @param AssessmentValue $assessmentValue
     */
    public function addAssessmentValue(AssessmentValue $assessmentValue)
    {
        $this->assessmentValues->add($assessmentValue);
    }

    /**
     * @param AssessmentValue $assessmentValue
     */
    public function removeAssessmentValue(AssessmentValue $assessmentValue)
    {
        $this->assessmentValues->removeElement($assessmentValue);
    }

    // ...
}

Model/GradePointAverage/AssessmentValue.php

namespace MyApp\Model\GradePointAverage;

use Doctrine\ORM\Mapping\Entity;
use Doctrine\ORM\Mapping\Table;
use Doctrine\ORM\Mapping\Column;
use Doctrine\ORM\Mapping\Id;
use Doctrine\ORM\Mapping\GeneratedValue;
use Doctrine\ORM\Mapping\ManyToOne;
use Doctrine\ORM\Mapping\JoinColumn;
use MyApp\Model\GradePointAverage;
use MyApp\Model\Assessment;
use MyApp\Util\ConstructorArgs;

/**
 * @Entity("MyApp\Repository\GradePointAverage\AssessmentValueRepository")
 * @Table("gpa_assessment_value")
 */
class AssessmentValue
{
    use ConstructorArgs;

    /**
     * @Id
     * @ManyToOne(targetEntity="MyApp\Model\GradePointAverage")
     */
    private $gradePointAverage;

    /**
     * @Id
     * @ManyToOne(targetEntity="MyApp\Model\Assessment")
     */
    private $assessment;

    /**
     * @Column("point_value")
     *
     * @var float
     */
    private $value;

    /**
     * @param array $args
     */
    public function __construct(array $args = [])
    {
        $this->handleArgs($args);
    }

    /**
     * @return GradePointAverage
     */
    public function getGradePointAverage()
    {
        return $this->gradePointAverage;
    }

    /**
     * @param GradePointAverage $gradePointAverage
     */
    public function setGradePointAverage(GradePointAverage $gradePointAverage)
    {
        $this->gradePointAverage = $gradePointAverage;
    }

    /**
     * @return Assessment
     */
    public function getAssessment()
    {
        return $this->assessment;
    }

    /**
     * @param Assessment $assessment
     */
    public function setAssessment(Assessment $assessment)
    {
        $this->assessment = $assessment;
    }

    /**
     * @return float
     */
    public function getValue()
    {
        return $this->value;
    }

    /**
     * @param float $value
     */
    public function setValue($value)
    {
        $this->value = $value;
    }

    /**
     * @return AssessmentScale
     */
    public function getAssessmentScale()
    {
        return $this->assessment->getScale();
    }
}

Model/Assessment.php

namespace MyApp\Model;

use Doctrine\ORM\Mapping\Entity;
use Doctrine\ORM\Mapping\Id;
use Doctrine\ORM\Mapping\GeneratedValue;
use Doctrine\ORM\Mapping\Column;
use Doctrine\ORM\Mapping\ManyToOne;
use MyApp\Model\Assessment\Scale;
use MyApp\Util\ConstructorArgs;

/**
 * @Entity("MyApp\Repository\AssessmentRepository")
 */
class Assessment
{
    use ConstructorArgs;

    /**
     * @Id
     * @GeneratedValue
     * @Column(type="bigint")
     *
     * @var int
     */
    private $id;

    // ...

    /**
     * @param array $args
     */
    public function __construct(array $args = [])
    {
        $this->handleArgs($args);
    }

    /**
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    // ...
}

Repository/GradePointAverageRepository.php

namespace MyApp\Repository;

use Doctrine\ORM\EntityRepository;
// ...
use MyApp\Model\GradePointAverage;

class GradePointAverageRepository extends BaseRepository implements GradePointAverageRepositoryInterface
{
    // ...

    /**
     * @param GradePointAverage $gradePointAverage
     */
    public function save(GradePointAverage $gradePointAverage)
    {
        $this->getEntityManager()->persist($gradePointAverage);
        $this->getEntityManager()->flush();
    }
}

Repository/GradePointAverage/AssessmentValueRepository.php

namespace MyApp\Repository\GradePointAverage;

use Doctrine\ORM\EntityRepository;
use MyApp\Model\GradePointAverage\AssessmentValue;

class AssessmentValueRepository extends EntityRepository
{
    /**
     * @param AssessmentValue $assessmentValue
     */
    public function save(AssessmentValue $assessmentValue)
    {
        $this->getEntityManager()->persist($assessmentValue);
        $this->getEntityManager()->flush();
    }
}

Manager/GradePointAverageManager.php

namespace MyApp\Manager;

use InvalidArgumentException;
use Symfony\Component\Validator\ValidatorInterface;
use JMS\DiExtraBundle\Annotation\Service;
use JMS\DiExtraBundle\Annotation\InjectParams;
use JMS\SecurityExtraBundle\Annotation\PreAuthorize;
use Knp\Component\Pager\Pagination\PaginationInterface;
use MyApp\Repository\GradePointAverageRepository;
use MyApp\PaginationFactory\GradePointAveragePaginationFactoryInterface;
use MyApp\Model\GradePointAverage;

/**
 * @Service("grade_point_average_manager")
 */
class GradePointAverageManager
{
    /**
     * @var GradePointAverageRepository
     */
    private $gradePointAverageRepository;

    /**
     * @var GradePointAveragePaginationFactoryInterface
     */
    private $gradePointAveragePaginationFactory;

    /**
     * @var ValidatorInterface
     */
    private $validator;

    /**
     * @InjectParams
     *
     * @param GradePointAverageRepository $gradePointAverageRepository
     * @param GradePointAveragePaginationFactoryInterface $gradePointAveragePaginationFactory
     * @param ValidatorInterface $validator
     */
    public function __construct(
        GradePointAverageRepository $gradePointAverageRepository,
        GradePointAveragePaginationFactoryInterface $gradePointAveragePaginationFactory,
        ValidatorInterface $validator
    )
    {
        $this->gradePointAverageRepository = $gradePointAverageRepository;
        $this->gradePointAveragePaginationFactory = $gradePointAveragePaginationFactory;
        $this->validator = $validator;
    }

    /**
     * @PreAuthorize("isAllowedToManageTheGradePointAverage(#gradePointAverage)")
     * @param GradePointAverage $gradePointAverage
     * @throws InvalidArgumentException
     */
    public function save(GradePointAverage $gradePointAverage)
    {
        $violationList = $this->validator->validate($gradePointAverage);
        if ($violationList->count()) {
            throw new InvalidArgumentException;
        }

        $this->gradePointAverageRepository->save($gradePointAverage);
    }
}

Controller/GradePointAverageController.php

namespace MyApp\Controller;

use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpKernel\Log\LoggerInterface;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use Doctrine\Common\Collections\ArrayCollection;
use FOS\RestBundle\View\View;
use JMS\DiExtraBundle\Annotation\Service;
use JMS\DiExtraBundle\Annotation\InjectParams;
use JMS\SecurityExtraBundle\Annotation\PreAuthorize;
use Knp\Component\Pager\Pagination\PaginationInterface;
use MyApp\Manager\GradePointAverageManager;
use MyApp\Model\GradePointAverage;
use MyApp\Model\GradePointAverage\AssessmentValue;

/**
 * @Service("grade_point_average_controller", parent="app.controller.abstract")
 * @Route("/gpa", service="grade_point_average_controller")
 */
class GradePointAverageController extends BaseController
{
    /**
     * @var GradePointAverageManager
     */
    private $gradePointAverageManager;

    private $logger;

    /**
     * @InjectParams
     *
     * @param GradePointAverageManager $gradePointAverageManager
     * @param LoggerInterface $logger
     */
    public function __construct(GradePointAverageManager $gradePointAverageManager, LoggerInterface $logger)
    {
        $this->gradePointAverageManager = $gradePointAverageManager;
        $this->logger = $logger;
    }

    // ...

    /**
     * @Route("/{id}", name="gpa.edit", requirements={"id" = "\d+"})
     * @Method("PUT")
     *
     * @param Request $request
     * @param GradePointAverage $gpa
     * @return View
     */
    public function editAction(Request $request, GradePointAverage $gpa)
    {
        $form = $this->formFactory->createNamed(null, 'gpa', $gpa, [
            'method' => 'PUT',
        ]);
        $form->handleRequest($request);

        foreach ($gpa->getAssessmentValues() as $av) {
            $this->logger->info('GPA ID PREVALIDATE IN CONTROLLER:'.$gpa->getId());
            $this->logger->info('PREVALIDATE IN CONTROLLER ASSESSMENT VAL ASSESSMENT ID:'.$av->getAssessment()->getId());
            $this->logger->info('PREVALIDATE IN CONTROLLER ASSESSMENT VAL POINTS:'.$av->getValue());
        }

        /*
        // try reversing the order of the collection to see if that helps
        $assessmentVals = $gpa->getAssessmentValues()->toArray();
        $reversed = array_reverse($assessmentVals);
        $reversedColl = new ArrayCollection($reversed);
        $gpa->setAssessmentValues($reversedColl);
        */

        if ($form->isValid()) {
            foreach ($gpa->getAssessmentValues() as $av) {
                $this->logger->info('GPA ID PRESAVE IN CONTROLLER:'.$gpa->getId());
                $this->logger->info('PRESAVE IN CONTROLLER ASSESSMENT VAL ASSESSMENT ID:'.$av->getAssessment()->getId());
                $this->logger->info('PRESAVE IN CONTROLLER ASSESSMENT VAL POINTS:'.$av->getValue());
            }
            $this->gradePointAverageManager->save($gpa);

            return new View($gpa, 204);
        }

        return new View($form);
    }

    // ...
}

do you have the sql generated from entity or you are generating entities from sql? — Gun2sh, Jan 02 '14 at 23:20
I'm writing the SQL by hand, then writing the entities by hand. We're not using generators in the project. — dyelawn, Jan 03 '14 at 15:08
can you generate the entities with doctrine command line and check your and their mapping file(just to be sure what you write in the entities is the same with the doctrine generated entities.) — Gun2sh, Jan 03 '14 at 16:08
I'll take a look, but I'm not sure how that would make anything different, because the generator would have to refer either to the existing schema or the existing entity mapping in the annotations. — dyelawn, Jan 03 '14 at 16:16
So to accomplish this, I generated mapping from the SQL file following this cookbook entry: http://symfony.com/doc/current/cookbook/doctrine/reverse_engineering.html. It is incapable of recognizing the join table with metadata. It just tries to map a `ManyToMany` between `GradePointAverage` and `Assessment` while ignoring the metadata field for point value. — dyelawn, Jan 03 '14 at 17:14
This is not a doctrine issue. The error occurs at the database level, therefore doctrine rollbacks. Try running the query by hand. — Dovydas Bartkevičius, Jan 08 '14 at 21:45
@DovydasBartkevičius I have successfully run the query by hand. The problem is the query *generated* by Doctrine. I've logged it out to see that it properly recognizes the existing entities as "managed" and new entities as "unmanaged". But at some point in SQL generation, it decides to issue a `CREATE` on an existing entity and an `UPDATE` on the new one, causing a duplicate primary key. See: http://www.doctrine-project.org/jira/browse/DDC-2879 — dyelawn, Jan 22 '14 at 14:54
I suspect it could be an issue with the form, rather than with Doctrine. The Symfony form component needs to merge form data back into an existing collection. During the merge it needs to decide if each item in the submission represents an update to an existing item or a new item. There doesn't seem to be a convention for matching these, and it's possible the procedure is complicated by composite foreign keys (because form posts are simple nested hashes). Double-check that your collection looks right, before you attempt to persist it. Also have a look at the 'MergeCollectionListener' class. — Ryan, May 20 '14 at 05:14

score 1 · Answer 1 · answered Nov 19 '14 at 20:52

Try to add an Id to the table gpa_assessment_value and then use command line to generate your entities. Mapping orm files from the database will not create the gpa_assessment_value.orm.yml if you don't add the id field.

CREATE TABLE gpa_assessment_value
(
    id                     bigserial       NOT NULL,
    grade_point_average_id bigint        NOT NULL,
    assessment_id          bigint        NOT NULL,
    point_value            numeric(4, 2) NOT NULL,

    PRIMARY KEY (id,assessment_id, grade_point_average_id),
    FOREIGN KEY (assessment_id) REFERENCES assessment,
    FOREIGN KEY (grade_point_average_id) REFERENCES grade_point_average
);