Using DTOs with groups #6284

New Issue

admin · 2026-01-22T15:30:08+01:00

admin commented

2026-01-22 15:30:08 +01:00

Originally created by @core23 on GitHub (Aug 19, 2019).

Originally assigned to: @core23 on GitHub.

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.

Originally created by @core23 on GitHub (Aug 19, 2019). Originally assigned to: @core23 on GitHub. ### 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: ```php $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.

admin added the Bug label 2026-01-22 15:30:08 +01:00

admin commented

2026-01-22 15:30:08 +01:00

@lcobucci commented on GitHub (Aug 19, 2019):

@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)')

@lcobucci commented on GitHub (Aug 19, 2019): @core23 I'd say that it would be more logical to have an alias for the entire DTO and use its properties. Like: ```php $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? ```php $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)') ```

admin commented

2026-01-22 15:30:09 +01:00

@core23 commented on GitHub (Aug 19, 2019):

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')

@core23 commented on GitHub (Aug 19, 2019): 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: ```diff $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') ```

admin commented

2026-01-22 15:30:09 +01:00

@lcobucci commented on GitHub (Aug 19, 2019):

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

@lcobucci commented on GitHub (Aug 19, 2019): How about adding new columns to the query and aliasing them?

admin commented

2026-01-22 15:30:09 +01:00

@core23 commented on GitHub (Aug 19, 2019):

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.

@core23 commented on GitHub (Aug 19, 2019): 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.

admin commented

2026-01-22 15:30:10 +01:00

@lcobucci commented on GitHub (Aug 20, 2019):

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

@lcobucci commented on GitHub (Aug 20, 2019): That sounds odd, could you send a PR with a functional test so that we can investigate that together?

admin referenced this issue

2026-01-22 16:05:52 +01:00

[PR #6284] #6217 when hydrating an entity with a composite primary key that is both an `EAGER` and a `LAZY` association and also cached, the `DefaultQueryCache` tries to pass L2 cache implementation detail objects to the `UnitOfWork` #9929

admin referenced this issue

2026-01-22 16:06:21 +01:00

[PR #6640] #6217 #6284 when hydrating an entity with a composite primary key that is both an `EAGER` and a `LAZY` association and also cached, the `DefaultQueryCache` tries to pass L2 cache implementation detail objects to the `UnitOfWork` #10079

Sign in to join this conversation.