Doctrine查询构建器给出了错误的结果

I have two entities Person and Nursery and I have a ManyToMany relation between them with a JoinTable. I want to make this 2 sql queries :

我有两个实体Person和Nursery,我和它们之间有一个与JoinTable有关的ManyToMany关系。我想做这2个SQL查询:

1) Find all staff (= Person) linked to the nursery with an nursery_id

1)使用nursery_id查找链接到托儿所的所有员工(= Person)

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) Find the staff with staff_id and be sure that he's linked to the nursery with nursery_id

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

For that I have this 2 queries in the PersonRepository :

为此,我在PersonRepository中有这两个查询:

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

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

But in both cases that queries doesn't care of the nursery_id and that gives me the staff undepended of the nursery_id. For example, the Person with id=4 is not linked with the nursery with id=2 but this query shows me this person.

但是在这两种情况下,查询都不关心nursery_id,这让我的工作人员不再使用nursery_id。例如,id = 4的Person未与id = 2的托儿所链接,但此查询向我显示此人。

EDIT : I have the same result with dql query :

编辑:我与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'"

2 个解决方案

#1

Maybe it's the way how you join the Nursery entity. Try to join it from the Person entity attribute like this:

也许这就是你加入幼儿园实体的方式。尝试从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();
}

This of course only works, if Person has the attribute nursery and it's mapped with Doctrine ORM. If the repository extends Doctrine\ORM\EntityRepository you can also simplify it:

这当然只有在Person具有属性苗圃并且使用Doctrine 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();
}

#2

Ok thanks to Sepultura my code works ! I dit this :

好的,谢谢Sepultura我的代码工作!我点了这个:

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

#1