Symfony2原则NotIn问题

Question

我想不出我做错了什么。我正在执行一个notin查询，其中我正在跟踪我发现的所有Stack溢出帖子，其中我说要先创建notin查询，然后将它放到实际的查询中。下面是我正在尝试运行的查询。

public function loadCompleted()
{
    $notIn = $this->getEntityManager()->createQueryBuilder()
        ->select('DISTINCT j.id')
        ->from($this->getEntityName(), 'j')
        ->join('NucleoManagerBundle:JobStatus', 'js', Join::WITH, 'j.jobStatus = js.id')
        ->join('NucleoManagerBundle:Task', 't', Join::WITH, 't.job = j.id')
        ->join('NucleoManagerBundle:TaskStatus', 'ts', Join::WITH, 't.taskStatus = ts.id');

    $notIn->where('ts.draft = 1')
        ->orWhere('ts.pending = 1')
        ->orWhere('ts.depending = 1')
        ->orWhere($notIn->expr()->andX(
            $notIn->expr()->eq('ts.draft', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.completed', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.pending', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.invoiced', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.cancelled', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.depending', $notIn->expr()->literal(false))
        ))
        ->getQuery()
        ->getResult();

    $query = $this->getEntityManager()->createQueryBuilder()
        ->select('j')
        ->from($this->getEntityName(), 'j')
        ->join('NucleoManagerBundle:JobStatus', 'js', Join::WITH, 'j.jobStatus = js.id')
        ->where('j.billable = 1')
        ->andWhere('j.invoiced = 0')
        ->andWhere('j.template = 0')
        ->andWhere('js.invoiced = 0')
        ->andWhere('js.cancelled = 0');

    $query->andWhere($query->expr()->notIn('j.id', $notIn));

    return $query->getQuery()->getResult();
}

我得到了以下错误：

ContextErrorException: Catchable Fatal Error: Object of class Doctrine\ORM\EntityManager could not be converted to string in C:\BitNami\wampstack-5.4.24-0\apps\manager\htdocs\vendor\doctrine\orm\lib\Doctrine\ORM\Query\Expr\Func.php line 76

有人能帮忙吗？提前感谢！

Answer 1

看起来我已经想出了如何这样做，通过这篇文章：Subquery in doctrine2 notIN Function。我在notin查询上使用了getDQL函数，并确保我的所有别名都与常规查询不一致。

public function loadCompleted()
{
    $notIn = $this->getEntityManager()->createQueryBuilder()
        ->select('DISTINCT j')
        ->from($this->getEntityName(), 'j')
        ->join('NucleoManagerBundle:JobStatus', 'js', Join::WITH, 'j.jobStatus = js.id')
        ->join('NucleoManagerBundle:Task', 't', Join::WITH, 't.job = j.id')
        ->join('NucleoManagerBundle:TaskStatus', 'ts', Join::WITH, 't.taskStatus = ts.id');

    $notIn->where('ts.draft = 1')
        ->orWhere('ts.pending = 1')
        ->orWhere('ts.depending = 1')
        ->orWhere($notIn->expr()->andX(
            $notIn->expr()->eq('ts.draft', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.completed', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.pending', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.invoiced', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.cancelled', $notIn->expr()->literal(false)),
            $notIn->expr()->eq('ts.depending', $notIn->expr()->literal(false))
        ));

    $query = $this->getEntityManager()->createQueryBuilder()
        ->select('job')
        ->from($this->getEntityName(), 'job')
        ->join('NucleoManagerBundle:JobStatus', 'jstatus', Join::WITH, 'job.jobStatus = jstatus.id')
        ->where('job.billable = 1')
        ->andWhere('job.invoiced = 0')
        ->andWhere('job.template = 0')
        ->andWhere('jstatus.invoiced = 0')
        ->andWhere('jstatus.cancelled = 0');

    $query->andWhere($query->expr()->notIn('job.id', $notIn->getDQL()));

    return $query->getQuery()->getResult();
}