Symfony 3 Too many parameters

Question

I'm new to Symfony, and I got an error while running a query :

public function getFilteredArticles($page, $nbPerPage, $data) {
        $query = $this->createQueryBuilder('a')
                ->leftJoin('a.images', 'i')
                ->addSelect('i')
                ->leftJoin('a.type_stockage', 't')
                ->addSelect('t')
                ->leftJoin('a.famille', 'f')
                ->addSelect('f');
        if ($data['famille'] != '') {
            $query->where('f.id = :famille')
                    ->setParameter('famille', $data['famille']);
        }
        if ($data['rds'] == false) {
            $query->where('a.stock_actuel > 0');
        }
        if ($data['recherche'] != '' && $data['recherche'] != null) {
            $query->where('a.ref_article LIKE :recherche')
                    ->setParameter('recherche', '%' . $data['recherche'] . '%');
        }
        $query->leftJoin('a.sousfamille', 's')
                ->orderBy('a.ref_article', 'ASC')
                ->getQuery();

        $query->setFirstResult(($page - 1) * $nbPerPage)
                ->setMaxResults($nbPerPage);

        return new Paginator($query, true);
    }

This query have conditionnals parameters as you can see, that returns the list of articles I need for a table. But when I run this query to fill my table, I got the error :

An exception has been thrown during the rendering of a template ("Too many parameters: the query defines 0 parameters and you bound 1").

I don't know why he is expecting 0 parameters. I tried using setParameters instead, but the result is the same.

Does anyone has an idea?

Jennie

Answer 1

You should use andWhere() methods instead of where().
where() method removes all previous where, but setParameter() does not. That's why he found more parameters than where clauses.

I personally never use where if the condition has no sense to be the first condition, to avoid this kinds of errors.

    if ($data['famille'] != '') {
        $query->andWhere('f.id = :famille')
                ->setParameter('famille', $data['famille']);
    }
    if ($data['rds'] == false) {
        $query->andWhere('a.stock_actuel > 0');
    }
    if ($data['recherche'] != '' && $data['recherche'] != null) {
        $query->andWhere('a.ref_article LIKE :recherche')
                ->setParameter('recherche', '%' . $data['recherche'] . '%');
    }

where() php doc

Specifies one or more restrictions to the query result.
Replaces any previously specified restrictions, if any.

andWhere() php doc

Adds one or more restrictions to the query results, forming a logical conjunction with any previously specified restrictions.

Seymourseys

Answer 2

My error, in Symfony 4, using Doctrine 2.6 was Too many parameters: the query defines 0 parameters and you bound 2

The problem was that I wasn't defining the parameters in andWhere method as

$this->createQueryBuilder('q')
...
->andWhere('q.propertyDate IS NOT NULL') //this also couldn't find anywhere
->andWhere('q.parameterName = :parameterName')
->setParameters(['q.parameterName' => $parameterName, ...2nd parameter])

As I couldn't find any answer to my problem, but was similar to this one, I thought to maybe help someone who is struggling like I was.

Apron

Answer 3

also in symfony 5 and 6 you should use andWhere() methods instead of where(). where() method removes all previous where, but setParameter() does not. That's why he found more parameters than where clauses.

Lungfish

Please do not duplicate existing answers, unless you want to add some new insights – Fuscous