Doctrine2 - Nested SELECT statements error using createQueryBuilder - "Invalid parameter number..."

Question

Context:

I am trying to convert this query

Select 
    *
FROM
    phases AS P
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            rating AS R
        WHERE
            R.phase_id = P.id AND R.user_id = 53)
LIMIT 1

into a couple of createQueryBuilder queries using this example

I have generated the following code:

    $subquery = $em->createQueryBuilder()
                ->select("r.id")
                ->from('AppDemoBundle:Rating', 'r')
                    ->innerjoin('r.user', 'u')
                    ->where('u.id = :_id')
                    ->setParameter('_id', 53)
                ->getDQL();
    $query = $em->createQueryBuilder()
                ->select('p')
                ->from('AppDemoBundle:Phases', 'p');
    $query->andWhere($query->expr()->notIn('p.id', $subquery));

    $phase = $query->getQuery()->getResult();

but I keep getting this error telling that the number of parameters is incorrect.

Doctrine\\ORM\\Query\\QueryException: Invalid parameter number: number of bound variables does not match number of tokens (uncaught exception) at C:\\wamp\\www\\demo\\vendor\\doctrine\\lib\\Doctrine\\ORM\\Query\\QueryException.php line 62

Question:

I would appreciate any help with this. It's my first time using the createQueryBuilder and I am not sure why this problem is happening. Thanks!

Did you try using `setParameter` on the outer query instead of the inner one? — Maerlyn, Jul 01 '14 at 15:38
@Maerlyn, Thank you! That worked like a charm. Please post it as an answer so I can accept it! — Georgi Angelov, Jul 01 '14 at 15:45

score 1 · Accepted Answer · answered Jul 01 '14 at 15:46

1

You need to call setParameter on the outer query instead of the outer one. Since that's the one you end up running it seems logical.

answered Jul 01 '14 at 15:46

Maerlyn

33,687
18
94
85

Any idea why the query is not working properly and returning all results? Does the logic match the SQL query that I am trying to execute? – Georgi Angelov Jul 01 '14 at 15:50
Use the web debug toolbar to see the query actually being executed, there you can see the differences between your original query and the generated one. – Maerlyn Jul 01 '14 at 15:52
for some reason, my toolbar does not record any of my DB interactions. – Georgi Angelov Jul 01 '14 at 16:03

Doctrine2 - Nested SELECT statements error using createQueryBuilder - "Invalid parameter number..."

1 Answers1