- 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:
- 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?