Symfony2 Doctrine querybuilder where IN

Question

I losted trilion hours google this but none of the solutions were good.

I have this querybuilder:

        $qb2=$this->createQueryBuilder('s')
        ->addSelect('u')
        ->innerJoin('s.user','u')
        ->where("u.id IN(:followeeIds)")
        ->andWhere('s.admin_status = false')
        ->setParameter('user', $user)
        ->setParameter('followeeIds', $arrayFolloweeIds)
        ->orderBy('s.id','DESC')
        ->setMaxResults(15)
    ;

I could do a second query and then do like $qb->getDQL() but have would i cache the query ?

Error:

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

score 76 · Accepted Answer · edited May 31 '16 at 10:15

76

You are setting the user parameter but I do not see it being used in the query anywhere?

Also I had issues with WHERE IN and Doctrine QueryBuilder with arrays would give me a similar error, and oddly enough running array_values before binding the parameter seemed to solve those issues as well.

Try:

$qb2=$this->createQueryBuilder('s')
        ->addSelect('u')
        ->innerJoin('s.user','u')
        ->where("u.id IN(:followeeIds)")
        ->andWhere('s.admin_status = false')
        ->setParameter('followeeIds', array_values($arrayFolloweeIds))
        ->orderBy('s.id','DESC')
        ->setMaxResults(15)
    ;

edited May 31 '16 at 10:15

pmaruszczyk

2,157
2
24
49

answered Apr 27 '13 at 17:47

Ghassan Idriss

2,120
20
16

2

omg i feel so stupid... of course the user! i didnt notice it ... and yes the array_values helped! And now finally magicly working $arrayFolloweeIds=implode("','",$arrayFolloweeIds) as well... i hope people will find it. thx ! – Lukas Lukac Apr 27 '13 at 18:08
`array_values` helped me, too. But why? I was using an indexed array. And on the other hand, with an delete query it worked without `array_values`. I am using doctrine/orm v2.5.4 – alpham8 Nov 22 '17 at 13:42
I think the issue is caused by an out of order numeric index on the array or possibly having string numeric indexes. Not 100% sure though but if thats the case that is why array_values works because it gives you a new array properly indexed – Ghassan Idriss Nov 22 '17 at 19:43

young B · Answer 2 · 2017-09-13T07:48:48.357

5

In Symfony2.8 the following example helps me

...
$qb2->where(
     $qb2->expr()->in('u.id', ':ids')
)
->setParameter('ids', $ids_array)
...

edited Sep 13 '17 at 07:48

answered Sep 13 '17 at 07:42

young B

127
1
11

1

nice, BTW @young it's a bad practice to name a variable based on it's value. E.g "array", "string". A meaningful name is more than enough: $ids for a list of IDs and $id if it's just a single integer :) – Lukas Lukac Nov 01 '17 at 20:03
Thanks, @EnchanterIO. I agree in this case. But PHP does not support explicit type. So how can I know which type of variable are using? As the example, I have a list of integers separated by a comma, or array of session_keys? Could you give more weighty arguments? Thanks :) – young B Nov 02 '17 at 10:08
sure. If you have a list of integers separated by a comma then you don't have an array, you have a string. So I would call it: commaConcatinatedIds. And migrate to PHP 7 to get strict types ;) if you have more questions you can reach me on twitter: https://twitter.com/EnchanterIO – Lukas Lukac Nov 02 '17 at 15:11
`$ids` implies multiple, so I'd expect an array. If it's not, i'd call it `$idsString` as it hold a string value and is an anomally. – Martijn May 30 '18 at 08:44

Symfony2 Doctrine querybuilder where IN

2 Answers2

Linked