Hi everonye,

I don’t know if this is still a topic for anyone. But here is a query that I 
came up with to do the sorting. It will currently probably not make use of the 
ltree indexing, so it might be worth to further adapt the query.

The table (example_table) would be something like

Top | 1
Top.Science | 1
Top.Hobbies | 2
Top.Collections | 3

The selection would work as follows:

/* create a intermediate table with an id column */
WITH ltreeTable AS (
  -- select the last part of the path as id
  subpath(path, -1) as "id",
  FROM example_table

/* split the ltree path into separate parts */
treeParts AS (
  -- split the path into separate parts
  unnest(regexp_split_to_array(path::text, '\.'))::ltree as "part",
  -- generate an ordinal for each array to preserve the order of the path
  generate_subscripts(regexp_split_to_array(path::text, '\.'), 1) as "idx"
  FROM ltreeTable

/* prefix each part with its respective zero-padded ordinal for sorting */
treePartsSorted AS (
  -- prefix each part with the ordinal
  lpad(b.ordinal::text, 4, '0') || '.' || a.part::text as "prefixed"
  FROM treeParts as a

  LEFT JOIN ltreeTable as b
  ON a.part = b.id<http://b.id>

/* combine the paths back again */
treeSorting AS (
  -- aggregate all parts and combine it back to an ltree path
  array_to_string(array_agg(prefixed ORDER BY idx),'.') AS "sorting"
  FROM treePartsSorted
  GROUP BY "id"

/* add the sorting column to the tree */
tree AS (
  a.*, text2ltree(b.sorting) as "sorting"
  FROM ltreeTable as a
  LEFT JOIN treeSorting as b
  ON a.id<http://a.id> = b.id<http://b.id>

ORDER BY sorting asc;

Reply via email to