Set parameter as int array for where in statement in dbal query builder throws array to string conversion exception

Question

I try to bind parameter : [1, 2] in dbal querybuilder in WHERE IN statement

I tried to change $qb->expr()->in() to string version but nothing changed

QueryBuilder creation

$qb = $this->_em->getConnection()->createQueryBuilder()
            ->select('c.id AS id')
            ->from('category', 'c')
            ->andWhere($qb->expr()->in('c.id', ':categories'))->setParameter('categories', [1, 2], \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);

Execution:

$qb->execute()->fetchAll();

Error : Array to string conversion

Expects to bind array of integers to querybuilder statement

On which line you get this error and how the first part is connected to the second? where does $qb go? What is $this and $filter? — Your Common Sense, Apr 19 '19 at 09:59
I just simplify code to show only problematic place. Fixed now. — Vytautas Saulis, Apr 20 '19 at 07:40

score 1 · Answer 1 · answered Apr 19 '19 at 11:19

always love to quote documentation.

// Example - $qb->expr()->in('u.id', array(1, 2, 3))
// Make sure that you do NOT use something similar to $qb->expr()->in('value', array('stringvalue')) as this will cause Doctrine to throw an Exception.
// Instead, use $qb->expr()->in('value', array('?1')) and bind your parameter to ?1 (see section above)
public function in($x, $y); // Returns Expr\Func instance

source: doctrine querybuilder docs

so essentially, unless the categories are numbers, you have to fill an array with placeholders and set those, or if they are numbers, you probably can just use the example.

score -1 · Answer 2 · answered Apr 19 '19 at 09:45

I assume you have an entity which has a ManyToMany or ManyToOne with a Category entity, You could just pass an array of Categories and it should work if the array is properly made like so :

qb = $this->_em->getConnection()->createQueryBuilder()
            ->select('c.id AS id')
            ->from('category', 'c')
     ->andWhere($qb->expr()->in('c.id',':categories'))->setParameter('categories', $categoriesArray);

Otherwise, you can try to use setParameter with your array of id by imploding the array :

qb = $this->_em->getConnection()->createQueryBuilder()
            ->select('c.id AS id')
            ->from('category', 'c')
            ->andWhere($qb->expr()->in('c.id', ':categories'))->setParameter('categories', implode(",",[1, 2]));

the first one is what exactly the OP does and the second one obviously won't work as expected — Your Common Sense, Apr 19 '19 at 09:54
The first one is not what the OP is doing, passing an Array of entities and an Array of integer is not the same thing. — Dylan KAS, Apr 19 '19 at 10:48

Set parameter as int array for where in statement in dbal query builder throws array to string conversion exception

2 Answers2