How to use PostgreSQL COLLATE modifier in Doctrine

Question

I use Doctrine and PostgreSQL database in my project and I would like to make it translatable. The problem is that I would like to sort some strings correctly related to the selected language customs.

Eg. in Czech language there is the letter "CH" located after the letter "H" in the aplhabet so I would like to see "Class", "Horn", "Chord" in the sorted list; however in English I would like to get "Chord", "Class", "Horn".

In Postgres I can create a partial index in the translations table and then use something like:

SELECT *
FROM "entity_translation"
WHERE "lang" = 1
ORDER BY "name" collate "en_US"

However I am unable (~I don't know how to do it) to use this syntax in Doctrine / DBAL.

Do you use something like that or any other solution for my use-case, please?

Answer 1

I fixed this by writing my own custom DQL function for doctrine. As shown here Using COLLATE inside Doctrine DQL Query (Symfony2).

This Doctrine Blog article provides detailed information on how to register the newly written function. The short version looks like

 $config = Setup::createAnnotationMetadataConfiguration($folderConfig, $devMode);
 $config->addCustomStringFunction('collate', Collate::class);
 $this->_entityManager = EntityManager::create($dbParams, $config);

The Query could look like (untested code)

$query = $queryBuilder->select('t.*')
            ->from(Translation::class, 't') 
              [...]         
            ->orderBy('collate(t.translatedText, en_US)')

Make sure that the OS or Container has the right locale installed.