logo标签列表

Doctrine ORM: 用由外键组成的复合主键持久化集合

symfonyormdoctrine-ormdoctrine-orm-postgres
10
我猜这是一个Doctrine的bug（我已经在JIRA问题跟踪器上提交了一个问题），但如果这只是用户错误，我决定在这里发布它。

概述

在具有由2个外键和一个元数据字段组成的复合主键的联接表中持久化实体集合在某些情况下会失败。代码基于这里的说明：Doctrine文档

问题详情

  1. SUCCESS: 当要持久化的集合中各项的 FOREIGN KEY 1 相同时，且 FOREIGN KEY 2 大于任何现有 PRIMARY KEY 中的 FOREIGN KEY 2 时，实体和相关实体将被正确持久化：

    • 示例：GPA “add val below” 已存在，具有评估值 {"assessment":6,"value":4}。我们将尝试添加一个新的评估值，其中 assessment_id > GPA “add val below”的任何现有评估值

    • 请求有效载荷：{"name":"add val below","courses":[],"assessmentValues":[{"assessment":6,"value":4},{"assessment":7,"value":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: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: 当要持久化的集合中各项的 FOREIGN KEY 1 相同时，且 FOREIGN KEY 2 小于任何现有 FOREIGN KEY 2 时，工作单元尝试插入现有实体，而不是操作新实体。

    • 示例：GPA “add val above” 已存在，具有评估值 {"assessment":8,"value":2}。我们将尝试添加一个新的评估值，其中 assessment_id < GPA “add val above”的任何现有评估值

    • 请求有效载荷：{"name":"add val above","courses":[],"assessmentValues":[{"assessment":6,"value":4},{"assessment":8,"value":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: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


      代码
      


      migration.sql
      


      创建评估表
(
    id       序列大整数 NOT NULL,
    scale_id 大整数    NOT NULL,
    title    字符串   NOT NULL,
    passing  布尔值   NOT NULL,
    rank     整数,
      主键 (id)
);
      创建评估等级表
(
    id   序列大整数 NOT NULL,
    name 字符串   NOT NULL,
      主键 (id)
);
-- ...
      创建平均绩点表
(
    id                         序列大整数       NOT NULL,
    name                       字符串         NOT NULL,
    additional_credit_allowance 数字(4, 2),
      主键 (id)
);
      -- ...
      创建GPA评估值表
(
    grade_point_average_id 大整数        NOT NULL,
    assessment_id          大整数        NOT NULL,
    point_value            数字(4, 2) NOT NULL,
      主键 (assessment_id, grade_point_average_id),
    外键 (assessment_id) REFERENCES assessment,
    外键 (grade_point_average_id) REFERENCES grade_point_average
);

      


      Model/GradePointAverage.php
      

      命名空间为MyApp\Model。
引入了Doctrine ORM的Entity、Id、GeneratedValue和Column注释，以及Doctrine Common的Collection和ArrayCollection类，以及MyApp\Util\ConstructorArgs和MyApp\Model\GradePointAverage\AssessmentValue两个类。
定义了一个名为GradePointAverage的类，作为实体映射到数据库表中，并指定了其对应的仓库类。
使用了ConstructorArgs trait，该trait提供了一个处理构造函数参数的方法。
定义了一个私有属性$id，作为实体在数据库中的唯一标识符，并且使用了Id和GeneratedValue注释指定了其生成策略和类型为bigint。
定义了一个私有属性$assessmentValues，用于存储与此平均成绩相关的所有评估值，并使用了OneToMany注释表示其与AssessmentValue实体之间是一对多的关系。
定义了构造函数用于初始化对象属性，并将$assessmentValues属性初始化为空ArrayCollection。
定义了获取和设置$assessmentValues属性的方法，以及添加和删除单个评估值的方法。


      Model/GradePointAverage/AssessmentValue.php
      

      

      命名空间为MyApp\Model\GradePointAverage；
使用了Doctrine ORM的实体（Entity）和表（Table）注解；
定义了三个属性：$gradePointAverage、$assessment和$value；
其中$gradePointAverage和$assessment属性都是Id，且分别指向MyApp\Model\GradePointAverage和MyApp\Model\Assessment模型；
$value属性对应数据库中的point_value字段，类型为float；
定义了构造函数__construct()和一些getter和setter方法。

      


      Model/Assessment.php
      


      命名空间 MyApp\Model;
      使用Doctrine\ORM\Mapping\Entity;
使用Doctrine\ORM\Mapping\Id;
使用Doctrine\ORM\Mapping\GeneratedValue;
使用Doctrine\ORM\Mapping\Column;
使用Doctrine\ORM\Mapping\ManyToOne;
使用MyApp\Model\Assessment\Scale;
使用MyApp\Util\ConstructorArgs;
      /**
 * @Entity("MyApp\Repository\AssessmentRepository")
 */
类 Assessment
{
    使用 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
      


      命名空间 MyApp\Repository;
      使用 Doctrine\ORM\EntityRepository;
// ...
使用 MyApp\Model\GradePointAverage;
      类 GradePointAverageRepository 扩展自 BaseRepository 并实现了 GradePointAverageRepositoryInterface 接口。
      /**
 * @param GradePointAverage $gradePointAverage
 */
public function save(GradePointAverage $gradePointAverage)
{
    $this->getEntityManager()->persist($gradePointAverage);
    $this->getEntityManager()->flush();
}

      


      Repository/GradePointAverage/AssessmentValueRepository.php
      


      命名空间 MyApp\Repository\GradePointAverage;
      使用 Doctrine\ORM\EntityRepository;
使用 MyApp\Model\GradePointAverage\AssessmentValue;
      类 AssessmentValueRepository 扩展 EntityRepository
{
    /**
     * @param AssessmentValue $assessmentValue
     */
    public function save(AssessmentValue $assessmentValue)
    {
        $this->getEntityManager()->persist($assessmentValue);
        $this->getEntityManager()->flush();
    }
}

      


      Manager/GradePointAverageManager.php
      

      

      命名空间 MyApp\Manager;
      使用无效参数异常;
使用 Symfony\Component\Validator\ValidatorInterface;
使用 JMS\DiExtraBundle\Annotation\Service;
使用 JMS\DiExtraBundle\Annotation\InjectParams;
使用 JMS\SecurityExtraBundle\Annotation\PreAuthorize;
使用 Knp\Component\Pager\Pagination\PaginationInterface;
使用 MyApp\Repository\GradePointAverageRepository;
使用 MyApp\PaginationFactory\GradePointAveragePaginationFactoryInterface;
使用 MyApp\Model\GradePointAverage;
      /**
 * @Service("grade_point_average_manager")
 */
类 GradePointAverageManager
{
    /**
     * @var GradePointAverageRepository
     */
    private $gradePointAverageRepository;
      /**
     * @var GradePointAveragePaginationFactoryInterface
     */
    private $gradePointAveragePaginationFactory;
      /**
     * @var ValidatorInterface
     */
    private $validator;
      /**
     * @InjectParams
     *
     * @param GradePointAverageRepository $gradePointAverageRepository
     * @param GradePointAveragePaginationFactoryInterface $gradePointAveragePaginationFactory
     * @param ValidatorInterface $validator
     */
    公共函数 __construct(
        GradePointAverageRepository $gradePointAverageRepository,
        GradePointAveragePaginationFactoryInterface $gradePointAveragePaginationFactory,
        ValidatorInterface $validator
    )
    {
        $this->gradePointAverageRepository = $gradePointAverageRepository;
        $this->gradePointAveragePaginationFactory = $gradePointAveragePaginationFactory;
        $this->validator = $validator;
    }
      /**
     * @PreAuthorize("isAllowedToManageTheGradePointAverage(#gradePointAverage)")
     * @param GradePointAverage $gradePointAverage
     * @throws InvalidArgumentException
     */
    公共函数 保存(GradePointAverage $gradePointAverage)
    {
        $violationList = $this->validator->validate($gradePointAverage);
        if ($violationList->count()) {
            抛出一个无效参数异常;
        }
       $this->gradePointAverageRepository->save($gradePointAverage);
    }
}

      


      Controller/GradePointAverageController.php
      

      

      命名空间 MyApp\Controller;
      使用 Symfony\Component\HttpFoundation\Request;
使用 Symfony\Component\HttpFoundation\Response;
使用 Symfony\Component\HttpKernel\Log\LoggerInterface;
使用 Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
使用 Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
使用 Doctrine\Common\Collections\ArrayCollection;
使用 FOS\RestBundle\View\View;
使用 JMS\DiExtraBundle\Annotation\Service;
使用 JMS\DiExtraBundle\Annotation\InjectParams;
使用 JMS\SecurityExtraBundle\Annotation\PreAuthorize;
使用 Knp\Component\Pager\Pagination\PaginationInterface;
使用 MyApp\Manager\GradePointAverageManager;
使用 MyApp\Model\GradePointAverage;
使用 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:'。$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:'。$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);
    }
      // ...
}
你有从实体生成的SQL吗？还是你正在从SQL生成实体？ - Gun2sh
我正在手写SQL，然后手写实体。我们在项目中没有使用生成器。 - dyelawn
我会看一下，但我不确定那会有什么不同，因为生成器必须引用现有模式或注释中的现有实体映射。 - dyelawn
为了实现这个目标，我按照这个食谱条目生成了来自SQL文件的映射：http://symfony.com/doc/current/cookbook/doctrine/reverse_engineering.html。它无法识别具有元数据的联接表。它只是尝试在GradePointAverage和Assessment之间映射一个ManyToMany，同时忽略点值的元数据字段。 - dyelawn
@DovydasBartkevičius 我已经成功手动运行了查询。问题在于Doctrine生成的查询。我已经记录下来，以便查看它是否正确地将现有实体识别为“已管理”，将新实体识别为“未管理”。但是在SQL生成的某个时候，它决定对现有实体发出CREATE命令，并对新实体发出UPDATE命令，从而导致重复的主键。请参见：http://www.doctrine-project.org/jira/browse/DDC-2879 - dyelawn
显示剩余3条评论
1个回答
1

尝试给表gpa_assessment_value添加一个id，然后使用命令行生成实体。如果您不添加id字段，从数据库映射orm文件将不会创建gpa_assessment_value.orm.yml。

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
);
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接