CODEWITHSUNDEEP
sqlpostgresqlltree
Zoltán Tamási
Zoltán Tamási

Reputation: 12809

PostgreSQL ltree find all ancestors of a given label (not path)

I have a table with an ltree path column. In my hierarchy the path to a label is unique, meaning that every label has exactly one parent label. In other words, there are no two ltree paths in the table which would end with the same label.

I have an ltree label, let's say "C".

I can find all descendant rows of this label with the following query:

select * from myTree where path ~ '*.C.*';

This works fine and gives the correct subtree.

Now I need to implement the query to find all ancestor rows of this given label. I mean if there are for example 3 rows in the table with labels "A", "A.B", "A.B.C", I would like to get the rows with paths "A" and "A.B" (probably including "A.B.C" itself, does not matter for now).

If I know the full path of "C" ("A.B.C" in the above example) then the task is easy with the @> operator. However, now I know only "C", and still I would like to achieve the task with a single query. Is there any way to do this?

Upvotes: 12

Views: 9553

Answers (2)

vyegorov
vyegorov

Reputation: 22905

Something like this:

WITH r AS
(
    SELECT *, row_number() OVER (ORDER BY path) rn
      FROM myTree
)
SELECT *
  FROM r
 WHERE rn <= (SELECT rn FROM r WHERE path ~ '*.C');

SQL Fiddle

Maybe there's a better way using built-in stuff though.

Upvotes: 0

Kouber Saparev
Kouber Saparev

Reputation: 8125

SELECT
  *
FROM
  myTree
WHERE
  path @> (
    SELECT
      path
    FROM
      myTree
    WHERE
      label = 'C'
  );

Upvotes: 23

Related Questions