How to perform a WHERE clause on a couple of columns with Doctrine?

user_7894520 0
Viewed 147

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?

symfony doctrine-orm sql php
1 Answers

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.

user_155861010
Related