phpmysqlsymfonydoctrinedbal

Doctrine DBAL setParameter() with array value

I'm using doctrine DBAL and have some problem with SQL query as result of a queryBuilder.

$builder = $this->getConnection()->getQueryBuilder();
$builder->select(['id','name','type'])
         ->from('table')
         ->where('id='.(int)$value)
         ->setMaxResults(1);
$builder->andWhere($builder->expr()->in('type', ['first','second']));

echo(builder->getSQL());

$data = $builder->execute()->fetchRow();

And get SQL

SELECT id, name, type FROM table WHERE (id=149) AND (type IN (first,second)) LIMIT 1

And this is the problem, I need that (type IN (first,second)) was encoded as strings like (type IN ('first','second'))

How to do that with query builder in the right way?

Solution

  • Try with

    $builder->andWhere('type IN (:string)');
$builder->setParameter('string', ['first','second'], Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

    Edit:

    This answer is quite old and, as stated in other answers, since Doctrine\DBAL\Connection::PARAM_STR_ARRAY has been deprecated in 3.6.0 (https://github.com/doctrine/dbal/blob/3.6.0/src/Connection.php), you should go with \Doctrine\DBAL\ArrayParameterType::STRING instead