php - Symfony 2 / Oracle Querybuilder result is case sensitive
483
I have a strange behavior with a query result in Symfony 2. I have data like this in an Oracle Database, in this exact order :
- BU 4 2015
- bU 2
- BU C
- BU S
- BU 4 2014
notice, the lower case in the second data
I Just want to display this list by alphabetical order, without any case sensitive support. The wanted result :
- BU C
- bU 2
- BU 4 2014
- BU 4 2015
- BU S
Now, here is the Querybuilder, made with Symfony 2.5.12 :
$queryBuilder = $this->createQueryBuilder('e');
$queryBuilder->orderBy('e.name');
But the query, show my list in this order :
- BU C
- BU 4 2014
- BU 4 2015
- BU S
- bU 2
When i play the query displayed in symfony 2 profiler in my Oracle database client, i get the wanted result.
When i dump the query builder result, I get the wrong order list.
I manage to solve my problem by this
$queryBuilder = $this->createQueryBuilder('e');
$queryBuilder
->select(e)
->addSelect('UPPER(e.name) as HIDDEN name')
->orderBy('name');
So, I just want to know why I get this strange behavior with the querybuilder ?
Thanks.
Answer
Solution:
First of all, your "wanted result" is not actually in alphabetical order. A case-insensitive sort would yield the following order:
Second of all, the way to do a case-insensitive sort with Oracle SQL is to use
UPPER()orLOWER()on the column(s) you want to sort by, like so:Finally, the way to do a case-insensitive sort with Doctrine QueryBuilder is exactly how you have done it: by adding
UPPER(e.name) as HIDDEN nameand then sorting by that field.There is no strange behavior by QueryBuilder in this example.