php - 继承和关联场景中的 Dotrine 查询问题

Question

我在 Symfony2 中有一个工作实现，具有以下模型的 Doctrine 。

• 家长可以申请一项或多项培训
• 培训基于特定技能，但您可以在不同日期针对同一技能进行多次培训。
• 家长参加培训后，他就可以被标记为与所参加的培训相关的技能“合格”并成为实习生。
• 家长可以参加针对同一技能的多次培训，但针对给定技能只能标记为“合格”一次
• 学员可以在许多不同的技能上获得“资格”

parent 和受训者之间的继承和关联(一对一)已使用单表继承实现，如下所示:

Parents\ParentsBundle\Entity\Parents:
type: entity
inheritanceType: SINGLE_TABLE
discriminatorColumn:
    name: type
    type: string
discriminatorMap:
     parents: Parents
     trainee: Parents\TraineeBundle\Entity\Trainee
table: Parents
repositoryClass: Parents\ParentsBundle\Repository\ParentsRepository
id:
    id:
        type: integer
        generator:
            strategy: AUTO
fields:
    firstname:
        type: string
        length: 250
    lastname:
        type: string
        length: 250
    dob:
        type: date
        nullable: true
lifecycleCallbacks:
    prePersist: [ setDateDeCreationValue ]
manyToMany:
    trainings:
        targetEntity: Training\TrainingBundle\Entity\Training
        mappedBy: parents
        orphanRemoval: true
        cascade: ["all"]
oneToOne:
    trainee:
        targetEntity: Parents\TraineeBundle\Entity\Trainee
        inversedBy: parents
        cascade:  ["all"]
        joinColumns:
            trainee_id:
                referencedColumnName: id
indexes:
    nom_prenoms_idx:
        columns: [ firstname, lastname ]


Parents\TraineeBundle\Entity\Trainee:
    type: entity
    extends: Parents\ParentsBundle\Entity\Parents
    repositoryClass: Parents\TraineeBundle\Repository\TraineeRepository
    manyToMany:
            skills:
                targetEntity: Training\SkillBundle\Entity\Skill
                mappedBy: trainees
    oneToOne:
            parents:
                targetEntity: Parents\ParentsBundle\Entity\Parents
                mappedBy: trainee

Trianing\SkillBundle\Entity\Skill:
    type: entity
    table: Skill
    repositoryClass: Training\SkillBundle\Repository\SkillRepository
    id:
        id:
            type: integer
            generator:
                strategy: auto
    fields:
        title:
            type: string
            length: 80
            unique: true
        description:
            type: string
            length: 250
            nullable: true
    manyToMany:
        trainees:
            targetEntity: Parents\TraineeBundle\Entity\Trainee
            inversedBy: skills
            cascade: ["all"]
            joinTable:
                name: trainess_skills
                joinColumns:
                    skill_id:
                        referencedColumnName: id
                        nullable: false
                        onDelete: CASCADE
                inverseJoinColumns:
                    trainee_id:
                        referencedColumnName: id
                        nullable: false
    uniqueConstraints:
        titre_UNIQUE:
            columns:
                - titre

但是，我希望将以下人员从“合格”培训组技能的家长列表中排除:

1. 学员已准备好掌握该技能

我确实有以下 SQL 查询，它使我能够获得预期的结果，但我无法在 Doctrine 中获得有效的查询，因为关联链接到实体。SQL查询

    SELECT p.*
  FROM Parents p
  LEFT JOIN training_formations tf
    ON p.id = tf.parents_id
  LEFT JOIN Training t
    ON tf.training_id = t.id
  LEFT JOIN Parents trainee
    ON p.intervenant_id = trainee.id
  LEFT JOIN trainees_skills ts
    ON trainee.id = ts.trainee_id
WHERE t.id=@trainingId and (t.skill_id <> ts.skill_id or p.trainee_id is null);

Doctrine 查询:

$qb = $this->createQueryBuilder('p');
        $qb->select('p')
            ->leftJoin('p.trainings', 't')
            ->leftJoin('p.trainee','tr')
            ->leftJoin('tr.skill','s')
            ->where('t.id = :trainingId')
            ->andWhere($qb->expr()->orX(
                    $qb->expr()->neq('t.skill','tr.skill'),
                    $qb->expr()->isNull('p.trainee')
                                        )
                    )
            ->setParameter('trainingId', $trainingId)
            ->orderBy('p.firstname', 'ASC');
        return $qb;

生成的查询抛出 PathExpression 错误，我试图通过在外键上使用“IDENTITY()”方法来纠正该错误，但没有成功。

我是否错过了什么或错误地实现了什么？

Answer 1

找到了解决方案，位于更新的 Doctrine 查询下方:

$qb = $this->createQueryBuilder('p');
    $qb->select('p')
        ->leftJoin('p.trainings', 't')
        ->leftJoin('p.trainee','tr')
        ->leftJoin('tr.skill','s')
        ->where('t.id = :trainingId')
        ->andWhere($qb->expr()->orX(
                $qb->expr()->neq('t.skill','s.id'),
                $qb->expr()->isNull('s.id')
                                    )
                )
        ->setParameter('trainingId', $trainingId)
        ->orderBy('p.firstname', 'ASC');
    return $qb;

但是，从各种阅读来看，组合似乎比这里使用的继承更适合。但这是另一个话题了。