Re: Ltree - how to sort nodes on parent node - Mailing list pgsql-general

From Saccilotto Ramon
Subject Re: Ltree - how to sort nodes on parent node
Date
Msg-id b2586fb4-542d-4338-94a9-8c93cfa1daeb@Spark
Whole thread Raw
In response to Ltree - how to sort nodes on parent node  (cojack <xcojack@gmail.com>)
List pgsql-general
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;

pgsql-general by date:

Previous
From: Luca Ferrari
Date:
Subject: Re: select version() with internal number version?
Next
From: Adrian Klaver
Date:
Subject: Re: multiple conflict targets