How natural sorting a varchar column with Doctrine

Question

I have a table with a column that contains names (varchar) but some names have numbers inside and the ordering is not the expected.

I have something like:

Courlis
D11 Espadon
D13 Balbuzard
D1 empacher
D2

But I expect:

Courlis
D1 empacher
D2
D11 Espadon
D13 Balbuzard

I've found lot of tips about it, but it's always on ordering numbers only stored as string: add a 0 to convert it in numbers, check the length of the string to place 1 before 10, etc... But in my case it can't work.

I can't use SQL query, because I use it in a form of a Symfony application that need a QueryBuilder.

It's just a way to speak, just it's not really what we expect when we order something. We expect order letters then when it followed by numbers, ordering it like: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. Not: 1, 10, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9 — mpiot, Feb 21 '20 at 20:10
Borderline duplicate of this questions. https://stackoverflow.com/questions/5967500/how-to-correctly-sort-a-string-with-a-number-inside — user2263572, Feb 21 '20 at 20:31
Mmm, I don't really understand the answer of the python question. It's not really related with Doctrine Query Builder. For the moment the only things I've find that work is a pure SQL request with `ORDER BY name::bytea ASC;` but it don't work in Doctrine. — mpiot, Feb 21 '20 at 20:40
You could do that in PostgreSQL from v10 on using ICU collations. — Laurenz Albe, Feb 21 '20 at 21:17
@LaurenzAlbe thanks for the info, I'll look into it. Do you have more info ? — mpiot, Feb 21 '20 at 21:25

score 3 · Answer 1 · answered Feb 21 '20 at 21:32

Here is a way to do this with ICU collations in PostgreSQL (available from v10 on):

CREATE COLLATION en_natural (
   LOCALE = 'en-US-u-kn-true',
   PROVIDER = 'icu'
);

CREATE TABLE atable (textcol text COLLATE en_natural);

COPY atable FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> Courlis
>> D11 Espadon
>> D13 Balbuzard
>> D1 empacher
>> D2
>> \.

test=# SELECT textcol FROM atable ORDER BY textcol;

    textcol    
---------------
 Courlis
 D1 empacher
 D2
 D11 Espadon
 D13 Balbuzard
(5 rows)

Thanks, it work well. I just search a way to use the solution with Doctrine. — mpiot, Feb 21 '20 at 21:43

score 1 · Answer 2 · answered Feb 21 '20 at 22:03

Thanks to Laurentz Albe for your answer,for a Step by step in a Symfony application:

Create a Migration file that create the custom collation

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20200221215657 extends AbstractMigration
{
    public function getDescription() : string
    {
        return '';
    }

    public function up(Schema $schema) : void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('CREATE COLLATION fr_natural (provider = "icu", locale = "fr-u-kn-true");');
    }

    public function down(Schema $schema) : void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('DROP COLLATION fr_natural');
    }
}

Create a Doctrine Custom Function (Collate DQL)

<?php

namespace App\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;

class Collate extends FunctionNode
{
    public $expressionToCollate = null;
    public $collation = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->expressionToCollate = $parser->StringPrimary();

        $parser->match(Lexer::T_COMMA);
        $parser->match(Lexer::T_IDENTIFIER);

        $lexer = $parser->getLexer();

        $this->collation = $lexer->token['value'];

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return sprintf( '%s COLLATE %s', $sqlWalker->walkStringPrimary($this->expressionToCollate), $this->collation );
    }
}

Register the new Function in the Doctrine config

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        server_version: '12'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App
        dql:
            string_functions:
                collate: App\DQL\Collate

And finally juste use it in the query when needed

$query = $this->createQueryBuilder('shell')
    ->orderBy('COLLATE(shell.name, fr_natural)', 'ASC')
    ->getQuery();

return $query->getResult();

How natural sorting a varchar column with Doctrine

2 Answers2

Linked

Related