graphile/graphile-engine

Use collation in order by

madtibo opened this issue · 3 comments

madtibo commented

Feature description

The order by for text column will use the default collation that will often not be a real lexicographical order. It would be nice to make a collation order available.

For example, in addition to the NAME_ASC order, a NAME_EN_ASC, NAME_FR_ASC, NAME_DE_ASC, ...

What would be the best way to obtain this feature (updating an existing plugin, writing a new plugin, ...)?

Motivating example

query:

query MyQuery {
  customerAddresses(orderBy: NAME_ASC) {
    nodes {
      name
      addressLine1
    }
  }
}

Result:

  "data": {
    "customerAddresses": {
      "nodes": [
        {
          "name": "Elévation",
          "addressLine1": "116 Rue de Turenne"
        },
        {
          "name": "Grossa",
          "addressLine1": "5 Schlüterstraße"
        },
        {
          "name": "Großbuchstabe",
          "addressLine1": "34 Rankestraße"
        },
        {
          "name": "Name 0001",
          "addressLine1": "29 rue poissonière"
        },
        {
          "name": "avanti",
          "addressLine1": "26 Calle Mármoles"
        },
        {
          "name": "échiquier",
          "addressLine1": "43 Rue Decamps"
        }
      ]
    }
  }
}

In SQL:

SELECT
  name,
  address_line_1
FROM
  customer_addresses
ORDER BY
  name,
  address_line_1;

Result:

     name      |   address_line_1   
---------------+--------------------
 Elévation     | 116 Rue de Turenne
 Grossc        | 5 Schlüterstraße
 Großbuchstabe | 34 Rankestraße
 Name 0001     | 29 rue poissonière
 avanti        | 26 Calle Mármoles
 échiquier     | 43 Rue Decamps
(6 lignes)

$ CREATE COLLATION de FROM "de-x-icu";
$ CREATE COLLATION en FROM "en-x-icu";
$ CREATE COLLATION fr FROM "fr-x-icu";
$ SELECT
  name,
  address_line_1
FROM
  customer_addresses
ORDER BY
  name COLLATE fr,
  address_line_1 COLLATE fr;

Result:

     name      |   address_line_1   
---------------+--------------------
 avanti        | 26 Calle Mármoles
 échiquier     | 43 Rue Decamps
 Elévation     | 116 Rue de Turenne
 Großbuchstabe | 34 Rankestraße
 Grossc        | 5 Schlüterstraße
 Name 0001     | 29 rue poissonière
(6 lignes)

Supporting development

I [tick all that apply]:

  • am interested in building this feature myself
  • am interested in collaborating on building this feature
  • am willing to help testing this feature before it's released
  • am willing to write a test-driven test suite for this feature (before it exists)
  • am a Graphile sponsor ❤️
  • have an active support or consultancy contract with Graphile
madtibo commented

ICU collation is not that different between english, french and german. Since I can modify the schema, I will specify the french collation for each column to get the desired order.

If ever there is a problem in the future with this collation, we can use a function to get the desired collation.

I close this issue for now.

benjie commented

You can also do this with makeAddPgTableOrderByPlugin: https://www.graphile.org/postgraphile/make-add-pg-table-order-by-plugin/

  • 👍1
madtibo commented

You can also do this with makeAddPgTableOrderByPlugin: https://www.graphile.org/postgraphile/make-add-pg-table-order-by-plugin/

Thanks a lot, i'll dig into it

  • 👍1