'CASE WHEN...' ok with addSelect() and addOrderBy(), but KO with addGroupBy() #9856
Description
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=9123Open 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 ASCtemporary 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