'Invalid parameter number: number of bound variables does not match number of tokens' Symfony

Question

I'm working on a symfony project entity with query builder. When I try to run this function I get this issue.

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

public function json_filterAllproductsAction() {

    $search = "";
    $category = 1;

    //Combine tables and create the query with querybuilder
    $em = $this->container->get('doctrine.orm.entity_manager');

    $qb = $em->createQueryBuilder();

    $qb->select('p')
            ->from('EagleAdminBundle:Products', 'p')
            ->orderBy('p.id', 'DESC');
    if ($category != 0) {
        $qb->where($qb->expr()->in('p.category', '?1'))
                ->setParameter(1, $category);
    }
    $qb->where('p.productTitle LIKE :title')
            ->setParameter('title', "$search%");

    //convert to json using "JMSSerializerBundle"
    $serializer = $this->container->get('serializer');
    $jsonproducts = $serializer->serialize($qb->getQuery()->getResult(), 'json');
    return new Response($jsonproducts);
}

I think error is in

$qb->where($qb->expr()->in('p.category', '?1')) ->setParameter(1, $category);

It would be great help someone can help me.

Answer 1

You have two issues here. The first is that your last where clause overwrites the first one. This can be fixed by using andWhere. The second is that your mixing named parameters (:title) with positional parameters (?1). Mixing is a no no. And you don't really need the expr object. Try:

$qb->select('product')
  ->from('EagleAdminBundle:Products', 'product')
  ->orderBy('product.id', 'DESC');
if ($category) {
    $qb->andWhere('product.category IN (:category)');
    $qb->setParameter('category', $category);
}
$qb->andWhere('product.productTitle LIKE :title');
$qb->setParameter('title', "$search%");