DQL and MAX function call as a CONCAT argument

Question

I have a repository function having the following instructions:

$QB = $this->_em->createQueryBuilder();
$QB ->addSelect("CONCAT(dsub2.dagId, MAX(tsub2.executionDate))")
    ->from(TaskInstance::class, 'tsub2')
    ->join('tsub2.dag', 'dsub2')
    ->addGroupBy('dsub2.dagId')
    ;

Calling $QB->getQuery()->getSQL(); or $QB->getQuery()->getResult() raises an error saying:

Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got 'MAX'

Replacing the addSelect call entirely with the following snippet doesn't work either and raises the same error:

->addSelect(
    $QB->expr()->concat(
        'dsub2.dagId'
        , $QB->expr()->max('tsub2.executionDate')
    )
)

Do you have any idea of the proper way (or a workaround) to nest MAX function call as a CONCAT argument?

Doctrine DBAL v2.6.3 ; Doctrine ORM v2.6.1

Answer 1

I have had the same issue, and i think it's a Doctrine Bug on v2.6.1. The tricks I found is more a workaround than a real solution :

You have to change

$QB->addSelect("CONCAT(dsub2.dagId, MAX(tsub2.executionDate))")

by

$QB->addSelect("CONCAT(dsub2.dagId, IF(true=true, MAX(tsub2.executionDate), ''))")

By inserting a IF (that includes the MAX), Doctrine will not "see" the MAX .

But... In the IF you need a condition and Doctrine doesn't like IF(true, 1, 0), so the other trick is the if condition : true=true.