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
),
/* 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 = b.id
)
SELECT * FROM tree
ORDER BY sorting asc;
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
),
/* 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 = b.id
)
SELECT * FROM tree
ORDER BY sorting asc;
