Create subquery using query builder

Question

I try to convert SQL query (which works fine) in Doctrine query builder but I do not succeed because it contains a sub query

This is my SQL Query :

SELECT * 
FROM `navigation` 
WHERE `parent_id` = 
(
     SELECT `id` 
     FROM `navigation` 
     WHERE `parent_id` = 47 
     AND `nav_type`= 'nav'
     AND `published` = 1 
     AND `title` = 'Top'
)

And this is what I tried in my Repository :

class NavigationRepository extends EntityRepository
{
    public function test($parentId, $type, $status, $title)
    {
        $subQuery = $this->createQueryBuilder('n2')
            ->select('n2.id')
            ->where('n2.parent = :parent')
            ->andWhere('n2.type = :type')
            ->andWhere('n2.status = :status')
            ->andWhere('n2.title = :title')
            ->setParameter('parent', $parentId)
            ->setParameter('type', $type)
            ->setParameter('status', $status)
            ->setParameter('title', $title)
            ->getDQL();

        $qb = $this->createQueryBuilder('n');
        $qb
            ->where(
                $qb->expr()->eq('n.parent', '('.$subQuery.')')
            )
            ->getQuery()
            ->getResult();

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

But I got this error :

QueryException: Invalid parameter number: number of bound variables does not match number of tokens

Why ? It seems I have the right number of parameter yet...

martin · Accepted Answer · 2016-09-09 09:44:22Z

2

You should call setParameter() on $qb instead of $subQuery. The DQL doesn't contain interpolated parameters, it's just a regular string.

answered Sep 9, 2016 at 9:44

martin

97.4k26 gold badges205 silver badges237 bronze badges

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

3 Comments

martin Over a year ago

I don't know what your query does or what your database looks like. You asked about the incorrect number of parameters.

Goku Over a year ago

yes indeed thanks, I update my answer with your solution and I put how I call my query

martin Over a year ago

Try dumping the SQL query with ->getQuery()->getSql() and manually check what it generated

Collectives™ on Stack Overflow

Create subquery using query builder

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related