1

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

Parktrip
  • 61
  • 4

0 Answers0