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 path|ordinal ----+-------------- 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 -- select the last part of the path as id subpath(path, -1) as "id", "path", "ordinal" FROM example_table ), /* split the ltree path into separate parts */ treeParts AS ( SELECT "id", -- 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 ( SELECT a.*, -- 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 ( SELECT "id", -- 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 ( SELECT 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> ) SELECT * FROM tree ORDER BY sorting asc;
