Symfony2 Doctrine NotIn issue

Question

I can't work out what I'm doing wrong. I am doing a notin query where I am following all the Stack overflow posts that I have found saying to create the notin query first then put it into the actual query. Here is the query I am trying to run.

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

And I get the following error:

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

Can anyone help?? Thanks in advance!

score 0 · Accepted Answer · edited May 23 '17 at 11:53

Looks like I figured out how to do it this way, through this post: Subquery in doctrine2 notIN Function. I use the getDQL function on the notin query and make sure that all my aliases don't coincide with the regular query ones.

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

Symfony2 Doctrine NotIn issue

1 Answers1