CODEWITHSUNDEEP
phpsymfonydoctrine-orm
John
John

Reputation: 1319

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

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?

Upvotes: 9

Views: 16476

Answers (5)

Ivan Pruchai
Ivan Pruchai

Reputation: 159

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)

Upvotes: 6

David Pugh
David Pugh

Reputation: 81

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.

Upvotes: 8

Virendra Jadeja
Virendra Jadeja

Reputation: 863

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();
    }

Upvotes: 1

Arcv
Arcv

Reputation: 279

Try this adding single quotes to '1'.

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

Upvotes: 0

Omis Brown
Omis Brown

Reputation: 199

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]);

Upvotes: 1

Related Questions