I'm using Symfony and Doctrine, and want to use DQL to match results where a couple of columns is IN an array of couples of values. I could write this in about 30 seconds in SQL, and it just works :
SELECT *
FROM table
WHERE (column1, column2)
IN (('value1', 'value2'), ('value3', 'value4'), ...);
But in DQL I cannot get it to work. I naively tried the following:
$values = [['value1', 'value2'], ['value3', 'value4']];
$this->createQueryBuilder('s')
->where('(s.column1, s.column2) IN (:values)')
->setParameter('values', $values)
->getQuery()
->getResult();
But I get the following error:
[Syntax Error] line 0, col 53: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','
So basically it won't let me use (s.column1, s.column2) in the WHERE clause.
I know I could solve my problem by looping on my values to build the query, or by using a native SQL query, but obviously I'd rather just be able to write a simple, clean DQL query. Is there a way to do this?
You can build a query using a foreach loop over an array of your columns:
$values = [
's.column1' => ['value1', 'value2'],
's.column2' =>['value3', 'value4']
];
$query = $this->createQueryBuilder('s')
where('s.id > 0');
$count = 0;
foreach ($columns as $column => $values) {
$paramName = $values_. $count;
$query = $query
->andWhere(sprintf(
'%s IN (:%s)',
$column,
$paramName;
))
->setParameter($paramName, $values);
$count++;
}
$query = $query
->getQuery()
->getResult();
As far as I know, there is no way in DQL to query multiple columns using your method.