php - 单个 Doctrine 连接表中的多个关系

Question

我有一个表通过一个(!)连接表与多个其他表相关。
这是一个粗略的草图:

pupils_table  (master table)
---
id  name

teachers_table  (related table 1)
---
id  first_name  last_name  ...

courses_table  (related table 2)
---
id  start_date  end_date  ...

join_table  (relates master to both (!) related tables)
---
id  master_id  teacher_id  course_id

获取完整学生及其教师和类(class)记录的标准 SQL 查询如下:

select p.*, t.*, c.* from pupils_table p 
left join join_table jt on jt.master_id = p.id
inner join teachers_table t on t.id = jt.teacher_id
inner join courses_table  c on c.id = jt.courses_id
;

但是我很难使用 Doctrine 的关系映射和 QueryBuilder 创建这样的查询。我发现的所有示例都使用连接表，该连接表将主表仅与一个相关表相关联。

这是我在实体中尝试的方法，如果我的主人:

/**
* @ORM\Table(name="pupils_table")
*/
class Pupils
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id()
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Teachers")
     * @ORM\JoinTable(name="join_table",
     *     joinColumns={@ORM\JoinColumn(name="master_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="teacher_id", referencedColumnName="id")}     * )
     */
    private $teachers;

    /**
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Courses")
     * @ORM\JoinTable(name="join_table",
     *     joinColumns={@ORM\JoinColumn(name="master_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="course_id", referencedColumnName="id")}     * )
     */
    private $courses;

    ...

到目前为止，我没有在教师和类(class)实体中引入任何特殊关系。

我的查询构建如下所示:

    $qb->select('p', 't', 'c')
        ->from('AppBundle:Pupils', 'p')
        ->innerJoin('p.teachers', 't')
        ->innerJoin('p.courses', 'c')
    ;

    $res = $qb->getQuery()->getResult();

结果是一个奇怪的 SQL 查询，其中包含错误的内连接序列，并且结果未正确嵌套相关数据(这是此 SQL 所期望的):

SELECT ... FROM pupils_table c0_
INNER JOIN join_table c3_ ON c0_.id = c3_.master_id 
INNER JOIN teachers_table c1_ ON c1_.id = c3_.teacher_id 
INNER JOIN join_table c4_ ON c0_.id = c4_.master_id 
INNER JOIN courses_table c2_ ON c2_.id = c4_.course_id 

如您所见，内连接之前没有左连接，并且 join_table 被连接了两次。我知道我没有在 QueryBuilder 命令中显式添加左联接，但是如果联接表仅在 DocBlock 注释中提到并且没有自己的实体(这是文档告诉我的)，我怎么能呢？

Answer 1

为了实现这种类型的映射，我会选择连接实体，它将保存对这 3 个实体的引用，并且该结构将生成您在帖子中提到的正确 SQL。下面是我们如何创建和关联连接实体的说明

首先不要直接链接这 3 个实体，而是通过以下方式将您的联结实体与这 3 个实体链接

PupilTeacherCourseHasMany ---ManyToOne---> Pupils
PupilTeacherCourseHasMany ---ManyToOne---> Teachers
PupilTeacherCourseHasMany ---ManyToOne---> Courses

Pupils ---OneToMany---> PupilTeacherCourseHasMany
Teachers ---OneToMany---> PupilTeacherCourseHasMany
Courses---OneToMany---> PupilTeacherCourseHasMany

学生类

/**
 * Pupils
 * @ORM\Table(name="pupils")
 * @ORM\Entity
 */
class Pupils
{
    /**
     * @ORM\OneToMany(targetEntity="NameSpace\YourBundle\Entity\PupilTeacherCourseHasMany", mappedBy="pupils",cascade={"your options"} )
     */
    protected $pupilTeacherCourseHasMany;

}

教师类

/**
 * Teachers
 * @ORM\Table(name="teachers")
 * @ORM\Entity
 */
class Teachers
{
    /**
     * @ORM\OneToMany(targetEntity="NameSpace\YourBundle\Entity\PupilTeacherCourseHasMany", mappedBy="teachers",cascade={"your options"} )
     */
    protected $pupilTeacherCourseHasMany;

}

类(class)类别

/**
 * Courses
 * @ORM\Table(name="courses")
 * @ORM\Entity
 */
class Courses
{
    /**
     * @ORM\OneToMany(targetEntity="NameSpace\YourBundle\Entity\PupilTeacherCourseHasMany", mappedBy="courses",cascade={"your options"} )
     */
    protected $pupilTeacherCourseHasMany;

}

学生教师类(class)有很多

/**
 * PupilTeacherCourseHasMany
 * @ORM\Table(name="pupilteachercoursehasmany")
 * @ORM\Entity
 */
class PupilTeacherCourseHasMany
{
    /**
     * @ORM\ManyToOne(targetEntity="NameSpace\YourBundle\Entity\Pupils", cascade={"your options"})
     * @ORM\JoinColumn(name="pupil_id", referencedColumnName="id")
     */
    protected $pupils;

    /**
     * @ORM\ManyToOne(targetEntity="NameSpace\YourBundle\Entity\Teachers", cascade={"your options"})
     * @ORM\JoinColumn(name="teacher_id", referencedColumnName="id")
     */
    protected $teachers;


    /**
     * @ORM\ManyToOne(targetEntity="NameSpace\YourBundle\Entity\Courses", cascade={"your options"})
     * @ORM\JoinColumn(name="course_id", referencedColumnName="id")
     */
    protected $courses;
}

现在要获取所有详细信息，您的 Doctrine 查询将类似于

$qb->select('p', 't', 'c')
    ->from('AppBundle:Pupils', 'p')
    ->leftJoin('AppBundle:PupilTeacherCourseHasMany ptc')
    ->innerJoin('ptc.teachers', 't')
    ->innerJoin('ptc.courses', 'c')
;
$res = $qb->getQuery()->getResult();

For reference see my another answer