How to implement subqueries in Symfony2 with Doctrine?

Question

I need to count the number of items returned in the subquery. If I write the subquery how DQL - all good, but if I try to build a query via QueryBuilder - I get an error.

Subquery DQL:

$qb3 = $this->createQueryBuilder('c')
            ->select('COUNT(c.id)')
            ->where('c.id IN (SELECT cl.id FROM Acme\AppBundle\Entity\ClassC cl INNER JOIN Acme\AppBundle\Entity\ClassP p WHERE p.var1 = :var1 AND p.var2 = cl.id GROUP BY cl.id)')
            ->setParameter('var1', $var);

Subquery via QueryBuilder:

$qb = $this->createQueryBuilder('c');
$qb->select('COUNT(c.id)')
   ->where(
       $qb->expr()->in(
           'c.id',
           $this->createQueryBuilder('cl')
                ->select('cl.id')
                ->innerJoin('Acme\AppBundle\Entity\ClassP', 'p')
                ->where('p.var1 = :var1')
                ->setParameter('var1', $var)
                ->andWhere('p.var2 = cl.id')
                ->groupBy('cl.id')
                ->getDQL()
     )
 );

Both versions return the same DQL.

Error: screen

Paweł Mikołajczuk · Accepted Answer · 2016-02-24 11:39:33Z

5

Try to move setParameter() to main level of query.

$qb = $this->createQueryBuilder('c');
$qb->select('COUNT(c.id)')
->where(
   $qb->expr()->in(
       'c.id',
       $this->createQueryBuilder('cl')
            ->select('cl.id')
            ->innerJoin('Acme\AppBundle\Entity\ClassP', 'p')
            ->where('p.var1 = :var1')
            ->andWhere('p.var2 = cl.id')
            ->groupBy('cl.id')
            ->getDQL()
 )
)
->setParameter('var1', $var);

answered Feb 24, 2016 at 11:39

Paweł Mikołajczuk

3,82227 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to implement subqueries in Symfony2 with Doctrine?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related