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

1. 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" [] []

2. 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);
    }

    // ...
}

Answer 1

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
);