Swan Mougnoz Swan Mougnoz - 9 days ago 6
PHP Question

Parameters being ignored with Doctrine QueryBuilder

I have a bunch of filters containing parameters that I would want to be included in my query using the QueryBuilder from Doctrine.

Here is my code :

$qb= $em->createQueryBuilder();
$query_qb = $qb
->from('padmaxResilieBundle:Subscriber','s')
->leftjoin('s.terminationPaper','l')
// ...
->leftjoin('c.cancelreasons','cr');

if ($filters) {
foreach ($filters as $value) {
$query_qb->andWhere($value);
}
}

$query_qb->setParameters($this->paramsMap);

$total = $query_qb->addSelect('COUNT(DISTINCT s)')
->getQuery()
->getResult();


Each element of the array $filters are string with 1 or more occurences of a parameter, ex :

$filters = array(
0 => "s.lastname LIKE '%:param1%' OR s.firstname LIKE '%:param1%'"
1 => "c.contractNumber LIKE '%:param2%'"
// ...
)


The parameter identifier is different for each filter and is referenced in the propertie paramMap like :

$this->paramMap = array(
"param1" => "my_value",
"param2" => "hello",
// ...
)


I'm assured that both the filter array and the paramMap are correct and references each other but I always got the error :

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


I can't figure what I'm doing wrong, any ideas ?

Answer

You cannot use parameters in strings like you are doing:

"c.contractNumber LIKE '%:param2%'"

You need to put the % characters in your parameters:

$filters = array(
    0 => "s.lastname LIKE :param1 OR s.firstname LIKE :param1"
    1 => "c.contractNumber LIKE :param2"
    // ...
)

$this->paramMap = array(
    "param1" => "%my_value%",
    "param2" => "%hello%",
    // ...
)
Comments