I am trying to sort alphabetically organisation names written in their national language, meaning they may contain accents or language specific characters.
I have found the solution in plain sql with Oracle
select org_name
from organisations
order by org_name COLLATE GENERIC_M;
which returns exactly what I what and sorted the way I want.
Now here comes the trouble when trying to do the same with Doctrine with the same schema using Oracle.
As I am using the query builder, I cannot just specify the part 'COLLATE GENERIC_M' in my query.
After having a look at doctrine documentation, I have tried the 'options' attribute annotation like this in my entity
/**
* @ORM\Column(type="string", length=255, name="org_name", options={"collation":"GENERIC_M"})
*/
private $name;
which doesn't work as apparently Doctrine doesn't support this attribute for Oracle vendor.
I have also tried to specify in my doctrine config like this:
driver: 'oci8'
service: true
user: '%env(DATABASE_USER)%'
password: '%env(DATABASE_PASS)%'
options:
1002: "SET NAMES 'UTF8' COLLATE 'GENERIC_M'"
but no changes (after clearing the cache of course).
I would like to avoid changing settings at schema level as it is a project that is already live and I don't want to introduce side-effects. I would like also to avoid using plain sql for this simple query as I use the doctrine pagination.
is there another way to achieve my primary goal ?
thank you in advance