从工作SQL访问OneToMany - ManyToMany DQL from working SQL, accessing OneToMany

ManyToMany DQL from working SQL, accessing OneToMany

本文关键字：OneToMany 访问 SQL 工作 | 更新日期: 2023-09-27

工作应用程序使用本地SQL查询从多多实体关系中获取项。试图翻译该查询的结果是大量的错误消息。Household实体与Reason有多对多关系。两个实体&它们之间的关系得到了适当的定义。连接表( housed_reason )没有在…/Entity中定义。我不明白的部分是如何加入Contact实体，Household实体与Contact实体有一个OneToMany关系。

工作SQL:

    select distinct r.reason
    from reason r
    join household_reason hr on hr.reason_id = r.id
    join household h on h.id = hr.household_id
    join contact c on c.household_id = h.id...

对于初学者来说，下面是Reason存储库中查询构建器的开头:

$query = $this->createQueryBuilder('r')->select('distinct r.reason')
    ->innerJoin('r.households', 'h')

现在有了复数形式的households，我如何指定与一个单数家庭的关系?

花了一些时间，但下面的工作:

    $query = $this->createQueryBuilder('r')->select('distinct r.reason')
        ->innerJoin('r.households', 'h')
        ->innerJoin('TruckeeProjectmanaBundle:Contact', 'c', 'WITH c.household = h.household')
        ->where('c.contactDate >= :startDate')
        ->andWhere('c.contactDate <= :endDate')
        ->andWhere('r.enabled = TRUE')
        ->orderBy('r.id')
        ->setParameters($dateCriteria)
        ->getQuery()
        ->getResult();