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?

Solution 1

Doctrine expression 'notIn' accepts array values in second argument. You have given query. Also, you should bind parameter using 'setParameter' to avoid injection. Please try this.

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

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

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

Solution 2

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.

Solution 3

Why not trying setParameters instead of setParameter ?

$q2 = $this->createQueryBuilder('c')
->select('DISTINCT (c2.shipId)')
->join('AppBundle:ShipsFleet', 'c2')
->where('c.userid = :uid')
->andWhere('c2.fleetId = :fid')
->setParameters(['fid'=> $fid,'uid'=> $uid]);