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

Dermatome

Answer 1

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)
    ;

Faulkner

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 ! – Dermatome

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 – Layoff

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 – Faulkner

Answer 2

In Symfony2.8 the following example helps me

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

Wince

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 :) – Dermatome

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 :) – Wince

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: twitter.com/EnchanterIO – Dermatome

$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. – Presbyterial