Sonata Admin Filter: Invalid parameter number: number of bound variables does not match number of tokens

Question

The following exception ...

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

... appears in Sonata Admin when I filter for two properties that are part of two different entities and each require a doctrine_orm_callback.

Specifically, in the code shown below, the exception appears when I fill the search fields that correspond to the "consultant_id" and "institute_name" properties. (i.e. fill both search fields in the browser).

Note: This exception really only happens when properties from different entities are used for filtering. On the other hand: When two properties of the same entity are used, the filtering works just fine.

Further information:

A full stacktrace can be found here: http://www.php-schulung.de/sonata-admin-filter-invalid-parameter-number-stacktrace/

I checked this out further by inserting a var_dump in the file where the exception is thrown:

vendor/doctrine/orm/lib/Doctrine/ORM/Query.php

// Prepare parameters
        $paramMappings = $this->_parserResult->getParameterMappings();

        var_dump($paramMappings);
        echo '--------------------------';
        var_dump($this->parameters);

        if (count($paramMappings) != count($this->parameters)) {
            throw QueryException::invalidParameterNumber();
        }

The variable $paramMappings indeed only contains one mapping, whereas $this->parameters contains both parameters just as it should.

This is what the filter definition looks like:

src/WF/ReviewBundle/Admin/BaseReviewAdmin.php       

  public function configureDatagridFilters(DatagridMapper $grid)
    {

        $grid->add('berater_name', 'doctrine_orm_callback', 
                array('callback'=>function($queryBuilder, $alias, $field, $value) {

                    if(empty($value['value']))
                        return;

                    $queryBuilder->from('WFConsultantBundle:ConsultantHasReview', 'cr_')
                            ->leftJoin('cr_.consultant', 'con')
                            ->where(sprintf('cr_.review = %s', $alias));

                    $search = new \WF\WFAdminBundle\Search\SearchName();
                    $search($queryBuilder, 'con', $field, $value);
                    return true;
            }));     

        $grid->add('sterne', 'doctrine_orm_callback', 
                array('callback'=>function($queryBuilder, $alias, $field, $value) {


                if(empty($value['value']))
                     return;

                $queryBuilder
                        ->from('WFReviewBundle:ReviewParameters', 'pr')
                        ->where(sprintf('pr.review = %s', $alias))
                        ->andWhere('pr.recommendWorth = :stars')
                        ->setParameter('stars', $value['value'])
                        ->distinct(false);

                    return true;}));

        $grid->add('institute_name', 'doctrine_orm_callback', 
                array('label'=>'Institut Name', 'callback'=>function($queryBuilder, $alias, $field, $value) {


                if(empty($value['value']))
                     return;

               $queryBuilder
                        ->from('WFConsultantBundle:ConsultantHasReview', 'cr')
                        ->from('WFInstituteBundle:OfficeHasInstitute', 'oi')
                        ->leftJoin('cr.consultant', 'c')                    
                        ->leftJoin('c.office', 'offi')
                        ->leftJoin('oi.institute', 'i')
                        ->where(sprintf('cr.review = %s', $alias))
                        ->andWhere('oi.office = offi')
                        ->andWhere('i.name = :institute')
                        ->setParameter('institute', $value['value'])
                        ->distinct(false);

                    return true;}));

        $grid->add('consultant_id', 'doctrine_orm_callback', array('label'=>'Berater ID', 'callback'=>function($queryBuilder, $alias, $field, $value) {

                    if(empty($value['value']))
                        return;

                    $queryBuilder->from('WFConsultantBundle:ConsultantHasReview', 'cr_')
                            ->leftJoin('cr_.consultant', 'con')
                            ->where(sprintf('cr_.review = %s', $alias))
                            ->andWhere('con.id = :consultant_id')
                            ->setParameter('consultant_id', $value['value']);

                    return true;

            }));     

        $grid->add('status');
        $grid->add('id', null, array('label'=>'Bewertung ID'));

    }

All three entities and their mapping definitions (in the annotations format) can be found here: http://www.php-schulung.de/sonata-admin-filter-invalid-parameter-number-entities/

Do you have any idea how I can get the filters to play together?

Or is this a Sonata bug?

Or a doctrine bug?

Any ideas?

Answer 1

I had the same issue. Change in your query

-> where(sprintf('cr_.review = %s', $alias))

for

-> andWhere(sprintf('cr_.review = %s', $alias))

You get into the middle of the query and you can not modify it.