Too few parameters: the query defines 1 parameters but you only bound 0

Question

Symfony Version 3.2.8

I am unsure what is causing this error, according to Doctrine Documentation the setParameter function is being used correctly?

Broken Code:

public function getNewShipChoices($uid, $fid) {
        /*Identify ships all ready added in fleet and do not allow them to be added again*/
        $q2 = $this->createQueryBuilder('c')
                    ->select('DISTINCT (c2.shipId)')
                    ->join('AppBundle:ShipsFleet', 'c2')
                    ->where('c.userid = :uid')->setParameter('uid', $uid)
                    ->andWhere('c2.fleetId = :fid')->setParameter('fid', $fid);

        $query = $this->createQueryBuilder('c3');
        $query->where($query->expr()->notIn('c3.shipId', $q2->getDQL()))->andWhere('c3.userid = :uid')->setParameter('uid', $uid);

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

Another thing I tried was to hard code the setParameter values, which brings the same error message

 ->where('c.userid = :uid')->setParameter('uid', 1)
                            ->andWhere('c2.fleetId = :fid')->setParameter('fid', 1);

Working Code: Replacing the setParameter with hard coded values instead of passing in 2 integer values of 1 and 1 works fine.

 public function getNewShipChoices($uid, $fid) {
        $q2 = $this->createQueryBuilder('c')
                    ->select('DISTINCT (c2.shipId)')
                    ->join('AppBundle:ShipsFleet', 'c2')
                    ->where('c.userid = 1')
                    ->andWhere('c2.fleetId = 1');

        $query = $this->createQueryBuilder('c3');
        $query->where($query->expr()->notIn('c3.shipId', $q2->getDQL()))->andWhere('c3.userid = 1');

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

Did I miss something completely obvious?

Answer 1

I believe the actual issue is caused by doctrine's ->getDQL command not passing through the parameter values. It passes through the expected parameters, but only hydrates them on execute.

In your example, you pass some DQL in to the select after already having set the fid parameter but don't re-set that parameter in the query that's executed, Doctrine does not know about that parameter so throws an error as expected.

The fix would be:

public function getNewShipChoices($uid, $fid) {
    /*Identify ships all ready added in fleet and do not allow them to be added again*/
    $q2 = $this->createQueryBuilder('c')
                ->select('DISTINCT (c2.shipId)')
                ->join('AppBundle:ShipsFleet', 'c2')
                ->where('c.userid = :uid')
                ->andWhere('c2.fleetId = :fid');

    $query = $this->createQueryBuilder('c3');
    $query->where($query->expr()->notIn('c3.shipId', $q2->getDQL()))
        ->andWhere('c3.userid = :uid')
        ->setParameter('uid', $uid)
        ->setParameter('fid', $fid);;

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

Note that $q2 has no set parameters, because these are thrown away when the DQL is passed.

Answer 2

For someone who looking for a solution for that problem.

In my case problem was in using setParameter and setParameters at the same query.

For example, I was having something like this:

if ($eventId) {
    $qb->andWhere("ec.event = :eventId");
    $qb->setParameter('eventId', $eventId);
}

if ($from && $to) {
    $qb
        ->andWhere($qb->expr()->between('ec.startDatetime', ':from', ':to'))
        ->setParameters([
            'from' => $from,
            'to' => $to,
        ]);
}

When we use the queryBuilder and we call setParameter first then setParameters in second, it overwrites the data defined with the setParameter.

So for me the solution was instead:

->setParameters([
    'from' => $from,
    'to' => $to,
]);

write:

 ->setParameter('from', $from)
 ->setParameter('to', $to)