Using DTOs with groups

[core23]

Bug Report

Q	A
BC Break	no
Version	2.6.3

Summary

Since Version 2.4, it is possible to create queries with DTO result objects using NEW operator. Unfortunately it is not possible to use field aliases with this.

Current behavior

There is a RuntimeException:

An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 95: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'as'").

How to reproduce

I use the https://github.com/beberlei/DoctrineExtensions library for MONTH, YEAR, COUNT and SUM support, but this should not be related to the actual problem.

Create the following query:

$repository
    ->createQueryBuilder('i')
    ->select(
        sprintf('new %s(
        MONTH(i.invoiceDate) as mn,
        COUNT(DISTINCT i.id),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100 * t.tax),
        YEAR(i.invoiceDate) as yr
        )
        ', StatisticViewModel::class)
    )
    ->leftJoin('i.positions', 'p')
    ->leftJoin('p.tax', 't')
    ->andWhere('i.invoiceDate is not null')
    ->addGroupBy('mn') 
    ->addGroupBy('yr') 

Expected behavior

I can use aliases in the select method and reuse them later when creating groups.

[lcobucci]

@core23 I'd say that it would be more logical to have an alias for the entire DTO and use its properties. Like:

$repository
    ->createQueryBuilder('i')
    ->select(
        sprintf('new %s(
        MONTH(i.invoiceDate),
        COUNT(DISTINCT i.id),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100 * t.tax),
        YEAR(i.invoiceDate)
        ) as dto
        ', StatisticViewModel::class)
    )
    ->leftJoin('i.positions', 'p')
    ->leftJoin('p.tax', 't')
    ->andWhere('i.invoiceDate is not null')
    ->addGroupBy('dto.month') 
    ->addGroupBy('dto.year') 

However, this creates complexity for an edge-case that I'm not sure that it makes sense to add to the ORM - depending on which level we take this it might create a much more complicated query than what's needed.

Why don't you use the code below instead?

$repository
    ->createQueryBuilder('i')
    ->select(
        sprintf('new %s(
        MONTH(i.invoiceDate),
        COUNT(DISTINCT i.id),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100 * t.tax),
        YEAR(i.invoiceDate)
        ) as dto
        ', StatisticViewModel::class)
    )
    ->leftJoin('i.positions', 'p')
    ->leftJoin('p.tax', 't')
    ->andWhere('i.invoiceDate is not null')
    ->addGroupBy('MONTH(i.invoiceDate)') 
    ->addGroupBy('YEAR(i.invoiceDate)') 

[core23]

Thanks for your response @lcobucci

I already tried your solution, but this produces a different QueryException:

[Semantical Error] line 0, col 515 near 'MONTH(i.invoiceDate),': Error: Cannot group by undefined identification or result variable.

I also trired using groups without an aggregate function (MONTH, YEAR, but this produces a different result:

$repository
    ->createQueryBuilder('i')
    ->select(
        sprintf('new %s(
        MONTH(i.invoiceDate),
        COUNT(DISTINCT i.id),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100),
        SUM(p.productPrice * p.quantity * (100 - i.discount) / 100 * t.tax),
        YEAR(i.invoiceDate)
        ) as dto
        ', StatisticViewModel::class)
    )
    ->leftJoin('i.positions', 'p')
    ->leftJoin('p.tax', 't')
    ->andWhere('i.invoiceDate is not null')
-    ->addGroupBy('MONTH(i.invoiceDate)') 
-    ->addGroupBy('YEAR(i.invoiceDate)') 
+    ->addGroupBy('i.invoiceDate') 

[lcobucci]

How about adding new columns to the query and aliasing them?

[core23]

You mean hidden columns? It's the same problem like using addGroupBy('MONTH(i.invoiceDate)'), because it's a new field in the select section.

[lcobucci]

That sounds odd, could you send a PR with a functional test so that we can investigate that together?