17

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?

jezmck
  • 1,138
  • 3
  • 18
  • 38
Sergey Nikolaev
  • 685
  • 1
  • 6
  • 16

2 Answers2

49

Try with

$builder->andWhere('type IN (:string)');
$builder->setParameter('string', ['first','second'], \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);
Matheus Gontijo
  • 1,178
  • 1
  • 12
  • 29
DonCallisto
  • 29,419
  • 9
  • 72
  • 100
  • 1
    Thank you, the second variant with \Doctrine\DBAL\Connection::PARAM_STR_ARRAY works great for me! – Sergey Nikolaev Jul 06 '15 at 11:53
  • First case does not work, because dbal don't wraps strings in array without parameter type. – Sergey Nikolaev Jul 06 '15 at 12:12
  • @SergeyNikolaev you are right but I wasn't able to test it so I've wrote an answer by trying to change array definiton and specifying parameter type – DonCallisto Jul 06 '15 at 12:14
  • Hopefully the yet-to-be-finished [SO Documentation](http://meta.stackoverflow.com/questions/303865/warlords-of-documentation-a-proposed-expansion-of-stack-overflow) will help fill these kinds of holes. It took me six hours of googling to find this answer. – user5670895 Sep 24 '15 at 18:19
  • `\Doctrine\DBAL\Connection::PARAM_STRING_ARRAY` is deprecated in newer versions. Use `\Doctrine\DBAL\ArrayParameterType::STRING` instead. – MarthyM Jul 25 '23 at 06:44
1
$builder
    ->andWhere($builder->expr()->in('type', ':types'))
    ->setParameter(':types', ['first','second'], \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);
Semercom
  • 11
  • 2