QueryBuilder: parameters in a loop

Question

I'm trying to build a request with SF2/Doctrine Querybuilder for a search field.

In my search field, I want to be able to type several keywords, and get the results which match with all keywords.

So, here is my code (in the repository):

foreach ($keywordsArray as $keyword)
    {
        $qb->andWhere($qb->expr()->orX(
            $qb->expr()->like('p.name', ':keyword'),
            $qb->expr()->like('p.surname', ':keyword')                    
        ));
        $qb->setParameter('keyword', '%'.$keyword.'%');
        var_dump($keyword);
    }

Problem: the parameter 'keyword' is always the last element of $keywordsArray...

So for example when I type "John Smith" in the search field, I have:

$keywordsArray

array (size=2)
    0 => string 'John' (length=4)
    1 => string 'Smith' (length=5)

The var_dump($keyword) inside the loop tells me that $keyword = John and then $keyword = Smith.

BUT, Symfony profiler tells me that the query executed is:

 SELECT [...]
 FROM [...]
 WHERE (l0_.name LIKE '%Smith%' OR a1_.surname LIKE '%Smith%') 
 AND (l0_.name LIKE '%Smith%' OR a1_.surname LIKE '%Smith%')

What I am expecting is:

 SELECT [...]
 FROM [...]
 WHERE (l0_.name LIKE '%John%' OR a1_.surname LIKE '%John%') 
 AND (l0_.name LIKE '%Smith%' OR a1_.surname LIKE '%Smith%')

Do you have any explanation/solution? What should I modify to solve the problem?

Answer 1

You should use unique parameters names, for example:

foreach ($keywordsArray as $id => $keyword)
{
    $qb->andWhere($qb->expr()->orX(
        $qb->expr()->like('p.name', ":keyword_".$id),
        $qb->expr()->like('p.surname', ":keyword_".$id)                    
    ));
    $qb->setParameter("keyword_".$id, '%'.$keyword.'%');
}

Because you every time replace keyword by new value