'CASE WHEN...' ok with addSelect() and addOrderBy(), but KO with addGroupBy() #6994

New Issue

admin · 2026-01-22T15:42:45+01:00

admin commented

2026-01-22 15:42:45 +01:00

Originally created by @spacecodeur on GitHub (Jun 17, 2022).

Bug Report

Q	A
BC Break	no
Version	2.12.3

Summary

On Symfony 6 and Doctrine 2.12.3, I build a new query.
In this query, I need to use 'CASE WHEN ...' in SELECT, ORDER BY and GROUP BY.

With the methods addSelect() and addOrderBy() all works fine. But I got an error if I use CASE WHEN in the parameter of the function addGroupBy(), the error is :

'Cannot group by undefined identification or result variable'

The bug still the same on sqlite3 or mariadb

Current behavior

For SELECT and ORDER BY, all work fine :

$query      ->addSelect("CASE
    WHEN content.col1 IS NOT NULL THEN 'foo'
    WHEN content.col2 IS NOT NULL THEN 'faa'
    ELSE 'fuu'
END AS col4added")

            ->addOrderBy('CASE
    WHEN content.col1 IS NOT NULL THEN content.col1
    WHEN content.col2 IS NOT NULL THEN content.col2
    ELSE content.col3
END','ASC')->getQuery()->getResult();

The query works without any error.
But if I use CASE WHEN ... in a addGroupBy() function :

...
$query      ->addSelect("CASE
    WHEN content.col1 IS NOT NULL THEN 'foo'
    WHEN content.col2 IS NOT NULL THEN 'faa'
    ELSE 'fuu'
END AS col4added")

            ->addOrderBy('CASE
    WHEN content.col1 IS NOT NULL THEN content.col1
    WHEN content.col2 IS NOT NULL THEN content.col2
    ELSE content.col3
END','ASC')

            ->addGroupBy('CASE 
    WHEN content.col1 IS NOT NULL THEN content.col1 
    ELSE content.col2
END')->getQuery()->getResult();

I get the error : ''Cannot group by undefined identification or result variable''

How to reproduce

With query builder, just add a addGroupBy using CASE WHEN and you'll get the error 'Cannot group by undefined identification or result variable'' if you execute the query.

I created a demo project for illustrate the bug. The link of the repo is here.

Here the commands for clone and deploy a local server for easily see the bug :

cd /tmp && \
git clone git@gitlab.com:spacecodeur/doctrineissuecasewheningroupby.git && \
cd doctrineIssueCaseWhenInGroupBy && \
composer install && \
./bin/console d:d:c && ./bin/console d:s:c && ./bin/console d:f:l -q && \
symfony server:start -d --port=9123

Open the URL https://127.0.0.1:9123 with your favorite browser. And then, you'll see the home page where the issue is showed :

And if you click on the link, to the bottom, you'll be redirected to a page using the query using addGroupBy() with CASE WHEN ... and get the error :

Expected behavior

The query does not return an error. Use GROUP BY CASE WHEN ... return the expected result.

With DB Browser tool, I get the generated query in the bundle profiler of Symfony. And I added GROUP BY CASE WHEN .... The query is executed without any error and the expected result of the query is showed :

Here the query if you want to copy/past :

SELECT c0_.id AS id_0, c0_.col1 AS col1_1, c0_.col2 AS col2_2, c0_.col3 AS col3_3, 
CASE 
	WHEN c0_.col1 IS NOT NULL THEN 'foo' 
	WHEN c0_.col2 IS NOT NULL THEN 'faa' 
	ELSE 'fuu' 
END AS sclr_4 

FROM content c0_ 

GROUP BY CASE
	WHEN c0_.col1 IS NOT NULL THEN c0_.col1 
    ELSE c0_.col2
END
	
ORDER BY CASE 
	WHEN c0_.col1 IS NOT NULL THEN c0_.col1 
	WHEN c0_.col2 IS NOT NULL THEN c0_.col2 
	ELSE c0_.col3 
END ASC

temporary solution

After the query builder, I can extract the SQL raw query (with $query->getQuery()->getSQL()). And then, add in the string the GROUP BY CAS WHEN ....
But with this approach, I can't get results as entity objects and its very annoying...

If someone has a better solution for skirt this (apparently) bug, I take !
I have an huge building query on my website (containing several conditions, and take more than 200 lines). I really really need to use groupBy() with CASE WHEN haha

Originally created by @spacecodeur on GitHub (Jun 17, 2022). ### Bug Report  | Q | A |------------ | ------ | BC Break | no | Version | 2.12.3 #### Summary On Symfony 6 and Doctrine 2.12.3, I build a new query. In this query, I need to use 'CASE WHEN ...' in `SELECT`, `ORDER BY` and `GROUP BY`. With the methods `addSelect()` and `addOrderBy()` all works fine. But I got an error if I use `CASE WHEN` in the parameter of the function `addGroupBy()`, the error is : 'Cannot group by undefined identification or result variable' **The bug still the same on sqlite3 or mariadb** #### Current behavior For `SELECT` and `ORDER BY`, all work fine : ```php $query ->addSelect("CASE WHEN content.col1 IS NOT NULL THEN 'foo' WHEN content.col2 IS NOT NULL THEN 'faa' ELSE 'fuu' END AS col4added") ->addOrderBy('CASE WHEN content.col1 IS NOT NULL THEN content.col1 WHEN content.col2 IS NOT NULL THEN content.col2 ELSE content.col3 END','ASC')->getQuery()->getResult(); ``` The query works without any error. But if I use `CASE WHEN ...` in a `addGroupBy()` function : ```php ... $query ->addSelect("CASE WHEN content.col1 IS NOT NULL THEN 'foo' WHEN content.col2 IS NOT NULL THEN 'faa' ELSE 'fuu' END AS col4added") ->addOrderBy('CASE WHEN content.col1 IS NOT NULL THEN content.col1 WHEN content.col2 IS NOT NULL THEN content.col2 ELSE content.col3 END','ASC') ->addGroupBy('CASE WHEN content.col1 IS NOT NULL THEN content.col1 ELSE content.col2 END')->getQuery()->getResult(); ``` I get the error : ''Cannot group by undefined identification or result variable'' #### How to reproduce With query builder, just add a `addGroupBy` using `CASE WHEN` and you'll get the error 'Cannot group by undefined identification or result variable'' if you execute the query. I created a demo project for illustrate the bug. The link of the repo [is here](https://gitlab.com/spacecodeur/doctrineissuecasewheningroupby). Here the commands for clone and deploy a local server for easily see the bug : ```bash cd /tmp && \ git clone git@gitlab.com:spacecodeur/doctrineissuecasewheningroupby.git && \ cd doctrineIssueCaseWhenInGroupBy && \ composer install && \ ./bin/console d:d:c && ./bin/console d:s:c && ./bin/console d:f:l -q && \ symfony server:start -d --port=9123 ``` Open the URL https://127.0.0.1:9123 with your favorite browser. And then, you'll see the home page where the issue is showed : ![homepage](https://i.ibb.co/sVxH9gX/Screenshot-from-2022-06-17-22-25-55.png) And if you click on the link, to the bottom, you'll be redirected to a page using the query using `addGroupBy()` with `CASE WHEN ...` and get the error : ![error](https://i.ibb.co/N9G2Zhj/Screenshot-from-2022-06-17-22-29-41.png) #### Expected behavior The query does not return an error. Use `GROUP BY CASE WHEN ...` return the expected result. With [DB Browser tool](https://sqlitebrowser.org/), I get the generated query in the bundle profiler of Symfony. And I added `GROUP BY CASE WHEN ...`. The query is executed without any error and the expected result of the query is showed : ![expected result](https://i.ibb.co/W3kFLny/Screenshot-from-2022-06-17-22-33-55.png) Here the query if you want to copy/past : ```sql SELECT c0_.id AS id_0, c0_.col1 AS col1_1, c0_.col2 AS col2_2, c0_.col3 AS col3_3, CASE WHEN c0_.col1 IS NOT NULL THEN 'foo' WHEN c0_.col2 IS NOT NULL THEN 'faa' ELSE 'fuu' END AS sclr_4 FROM content c0_ GROUP BY CASE WHEN c0_.col1 IS NOT NULL THEN c0_.col1 ELSE c0_.col2 END ORDER BY CASE WHEN c0_.col1 IS NOT NULL THEN c0_.col1 WHEN c0_.col2 IS NOT NULL THEN c0_.col2 ELSE c0_.col3 END ASC ``` #### temporary solution After the query builder, I can extract the SQL raw query (with `$query->getQuery()->getSQL()`). And then, add in the string the `GROUP BY CAS WHEN ...`. But with this approach, I can't get results as entity objects and its very annoying... If someone has a better solution for skirt this (apparently) bug, I take ! I have an huge building query on my website (containing several conditions, and take more than 200 lines). I really really need to use `groupBy()` with `CASE WHEN` haha

admin closed this issue

2026-01-22 15:42:45 +01:00

admin commented

2026-01-22 15:42:46 +01:00

@greg0ire commented on GitHub (Jun 17, 2022):

The bug still the same on sqlite3 or mariadb

I don't think it's a bug, see https://www.doctrine-project.org/projects/doctrine-orm/en/2.12/reference/dql-doctrine-query-language.html#items

GroupByItem ::= IdentificationVariable | ResultVariable | SingleValuedPathExpression

I think ResultVariable might help you:

/* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */
ResultVariable = identifier

Try aliasing the case to a variable, then group by that variable?

@greg0ire commented on GitHub (Jun 17, 2022): > The bug still the same on sqlite3 or mariadb I don't think it's a bug, see https://www.doctrine-project.org/projects/doctrine-orm/en/2.12/reference/dql-doctrine-query-language.html#items `GroupByItem ::= IdentificationVariable | ResultVariable | SingleValuedPathExpression` I think `ResultVariable` might help you: ``` /* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */ ResultVariable = identifier ``` Try aliasing the case to a variable, then group by that variable?

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6994

'CASE WHEN...' ok with addSelect() and addOrderBy(), but KO with addGroupBy() #6994