继承和关联方案中的Doctrine Query问题

Question

I've a working implementation in Symfony2 with doctrine of the following model.

• A Parent can apply for one/more Training(s)
• A Training is based on a specific Skill, but you can have multiple trainings at different date for the same skill.
• Once a Parent attends the training he can be marked as 'qualified' for the Skill related to the Training taken and becomes a Trainee.
• A Parent can attend multiple Training on the same Skill but will only marked 'qualified' only once for the given Skill
• The Trainee can be 'qualified' on many differents Skills

The Inheritance and Association (one to one) between Parents and Trainee has been implemented using a single table inheritance as seen below:

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

However, im looking to exclude from the list of Parents to be 'qualified' on a Skill from a Training group those who are:

1. All ready Trainee on that Skill

I do have the following SQL Query which enable me to get the expected result, but i'm unable to have a working one in Doctrine since the associations links to Entities. SQL Query

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

The Doctrine Query:

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

The resulting query throws a PathExpression error, which i tempted to correct by using the 'IDENTITY()' method on the foreign keys but ut did not work.

Did i miss something or worongly implemented something?

Answer 1

Found the solution,below the updated Doctrine Query:

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

However, from various reading it seems that a Composition will be better suited than the Inheritance used here. But that's another topic.