Subquery in Doctrine2 Notin Function

Subquery in doctrine2 notIN Function

The same alias cannot be defined 2 times in the same query

$qb  = $this->_em->createQueryBuilder();
$qb2 = $qb;
$qb2->select('m.id')
    ->from('Custom\Entity\MembreService', 'ms')
    ->leftJoin('ms.membre', 'm')
    ->where('ms.id != ?1');

$qb  = $this->_em->createQueryBuilder();
$qb->select('mm')
    ->from('Custom\Entity\Membre', 'mm')
    ->where($qb->expr()->notIn('mm.id', $qb2->getDQL())
);
$qb->setParameter(1, $service);
$query  = $qb->getQuery();

return $query->getResult();

Ideally you should use many-to-many relation for your entity, in this case your query is going to be much simpler.

Join subquery with doctrine 2 DBAL

I've found a solution by adapting this DQL example to DBAL. The trick is to get the raw SQL of the subquery, wrap it in brackets, and join it. Parameters used in the subquery must be set in the main query:

$subSelect = $connection->createQueryBuilder()
    ->select(array('userSurveyID', 'MIN(timestamp) timestamp'))
    ->from('user_survey_status_entries')
    // Instead of setting the parameter in the main query below, it could be quoted here:
    // ->where('status = ' . $connection->quote(UserSurveyStatus::ACCESSED))
    ->where('status = :status')
    ->groupBy('userSurveyID');

$select = $connection->createQueryBuilder()
    ->select($selectColNames)
    ->from('user_surveys', 'us')
    // Get raw subquery SQL and wrap in brackets.
    ->leftJoin('us', sprintf('(%s)', $subSelect->getSQL()), 'firstAccess', 'us.userSurveyID = firstAccess.userSurveyID')
    // Parameter used in subquery must be set in main query.
    ->setParameter('status', UserSurveyStatus::ACCESSED)
    ->where('us.surveyID = :surveyID')->setParameter('surveyID', $surveyID);

Error with subquery in doctrine2 query builder

This issue is about objects and references in PHP.

When you do $subquery = $query;, $query being an object, you simply have $subquery pointing to the same value.

A PHP reference is an alias, which allows two different variables to
write to the same value. As of PHP 5, an object variable doesn't
contain the object itself as value anymore. It only contains an object
identifier which allows object accessors to find the actual object.
When an object is [...] assigned to another
variable, the different variables are not aliases: they hold a copy of
the identifier, which points to the same object.

Reference: http://us1.php.net/manual/en/language.oop5.references.php

It means in your code that when you write this:

$subquery
    ->select('f.following')
    ->from('ApiBundle:Follow', 'f')
    ->where('f.follower = :follower_id')
    ->setParameter('follower_id', $id)
;

This is equivalent to:

$query
    ->select('f.following')
    ->from('ApiBundle:Follow', 'f')
    ->where('f.follower = :follower_id')
    ->setParameter('follower_id', $id)
;

So when at the end you call:

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

You are using 2 times the same object pointed by 2 different variables ($query === $subquery).

To solve this issue, you can either use:

$query = $this->getEntityManager()->createQueryBuilder();
$subquery = $this->getEntityManager()->createQueryBuilder();

Or the clone keyword:

$query = $this->getEntityManager()->createQueryBuilder();
$subquery = clone $query;

Symfony2 Doctrine NotIn issue

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

doctrine not exists subquery

You can achieve it with something like this:

    ....
    // construct a subselect joined with an entity that have a relation with the first table as example user
    $sub = $this->createQueryBuilder();
    $sub->select("t");
    $sub->from("AnotherEntity","t");
    $sub->andWhere('t.user = j.id');

    // Your query builder:
    $qb->andWhere($qb->expr()->not($qb->expr()->exists($sub->getDQL())));

Hope this help

---

Related Topics