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?

Victor Bocharsky · Accepted Answer · 2015-09-02T19:27:52.060

8

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

edited Sep 02 '15 at 19:27

answered Jan 23 '14 at 10:16

Victor Bocharsky

11,930
13
58
91

Works perfectly, thanks. But why did you edit you answer from ":keyword_".$id to ":keyword_$id"? The first works, not the second. – Blacksad Jan 23 '14 at 10:30
You can use or `":keyword_".$id` or `":keyword_$id"`. It all must to work. I like second, because it without concatenation, but you necessarily must to use double quotes! – Victor Bocharsky Jan 23 '14 at 10:35
Hum, I get `[Syntax Error] line 0, col 112: Error: Expected end of string, got '$'` when I try the second. But I'm fine with the first anyway, thanks :-) – Blacksad Jan 23 '14 at 10:38

QueryBuilder: parameters in a loop

1 Answers1