timon.schroeter timon.schroeter - 2 months ago 23
PHP Question

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

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

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.