原则查询生成器给出错误的结果

Question

我有两个实体 Person 和 Nursery，它们之间有一个ManyToMany关系，有一个JoinTable。我想进行这个 2 个 sql 查询：

1） 找到与托儿所相关的所有员工（= 人）nursery_id

select p.* from person p inner join nursery_staff ns on p.id = ns.staff_id inner join nursery n on ns.nursery_id = n.id where n.id=1 and p.nursery_staff_role <> 'MANAGER';

2）找到有staff_id的工作人员，并确保他与托儿所有联系nursery_id

select p.* from person p inner join nursery_staff ns on p.id = ns.staff_id inner join nursery n on ns.nursery_id = n.id where n.id=2 and p.id=4 and p.nursery_staff_role <> 'MANAGER';

为此，我在 PersonRepository 中有以下 2 个查询：

1 ）

public function findAllStaffLinkedToANursery($nursery_id)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('p')
        ->from($this->_entityName, 'p')
        ->innerJoin('VSCrmBundle:Nursery', 'n')
        ->where('n.id = :id')
        ->andWhere('p.nurseryRole <> :profession')
        ->setParameters(array('id' => $nursery_id, 'profession' => 'MANAGER'));
    return $qb->getQuery()->getResult();
}

2）

public function findOneByNurseryAndStaffId($nursery_id, $staff_id)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('p')
        ->from($this->_entityName, 'p')
        ->innerJoin('VSCrmBundle:Nursery', 'n')
        ->where('p.id = :pid')
        ->andWhere('n.id = :nid')
        ->andWhere('p.nurseryRole <> :staffRole')
        ->setParameters(array(
            'pid' => $staff_id,
            'nid' => $nursery_id,
            'staffRole' => 'MANAGER'
        ));
    return $qb->getQuery()->getOneOrNullResult();
}

但在这两种情况下，查询都不关心nursery_id，这给了我不受nursery_id依赖的工作人员。例如，id=4 的人与 id=2 的托儿所没有链接，但此查询向我显示了此人。

编辑：我对 dql 查询有相同的结果：

 php bin/console doctrine:query:dql "select p.email from VSCrmBundle:Person p inner join VSCrmBundle:Nursery n where n.id=2 and p.nurseryRole <> 'MANAGER'"

Answer 1

也许这是您加入托儿所实体的方式。尝试从 Person 实体属性加入它，如下所示：

public function findAllStaffLinkedToANursery($nursery_id)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('p')
        ->from($this->_entityName, 'p')
        ->innerJoin('p.nursery', 'n')
        ->where('n.id = :id')
        ->andWhere('p.nurseryRole <> :profession')
        ->setParameters(array('id' => $nursery_id, 'profession' => 'MANAGER'));
    return $qb->getQuery()->getResult();
}

这当然只在人具有托儿所属性并且与教义 ORM 映射的情况下才有效。如果存储库扩展了 Doctrine\ORM\EntityRepository，您还可以简化它：

public function findAllStaffLinkedToANursery($nursery_id)
{
    $qb = $this->createQueryBuilder('p');
    $qb->innerJoin('p.nursery', 'n')
        ->where('n.id = :id')
        ->andWhere('p.nurseryRole <> :profession')
        ->setParameters(array('id' => $nursery_id, 'profession' => 'MANAGER'));
    return $qb->getQuery()->getResult();
}

Answer 2

好的，感谢塞普尔图拉，我的代码可以工作！我说这个：

public function findAllStaffLinkedToANursery($nursery_id)
{
    $qb = $this->createQueryBuilder('p');
    $qb->innerJoin('p.nurseries', 'n')
        ->where('n.id = :id')
        ->andWhere('p.nurseryRole <> :profession')
        ->setParameters(array('id' => $nursery_id, 'profession' => 'MANAGER'));
    return $qb->getQuery()->getResult();
}