CODEWITHSUNDEEP
sqlpostgresqlhierarchical-datarecursive-query
S. Kaio
S. Kaio

Reputation: 55

How to select all the hierarchy of a table with levels in PostgreSQL

I have a problem in this moment. I have a table called places with this structure:

I want to do a selection to have all the hierarchy of this table. There's a little example of data:

(1, null, '123 Barclay St')
(2, 1, 'Floor 1')
(3, 1, 'Floor 2')
(4, 1, 'Floor 3')
(5, 2, 'Hall 1')
(6, 2, 'Room 1')
(7, 2, 'Room 2')
(8, 3, 'Room 3')
(9, null, '10 Thames St')

Obviously the order in the table is not this one.

So I want to get this result with my SELECT (with the 9 rows):

123 Barclay St
   Floor 1
      Hall 1
      Room 1
      Room 2
   Floor 2
      Room 3
   Floor 3
10 Thames St

And not this result (that I already know how to get) :

10 Thames St
123 Barclay St
   Floor 1
   Floor 2
   Floor 3
      Hall 1
      Room 1
      Room 2
      Room 3

If you can help me, I thank you in advance.

Upvotes: 3

Views: 6045

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Here is a solution using recursive CTEs:

WITH RECURSIVE cte AS (
    SELECT LPAD(id::text, 3, '0') AS marker, '   ' AS buffer,
        id, parent_id, name::text
    FROM yourTable t WHERE parent_id IS NULL
    FROM yourTable t WHERE parent_id IS NULL
    UNION ALL
        SELECT t2.marker || ':' || LPAD(t1.parent_id::text, 3, '0') || ':' ||
            LPAD(t1.id::text, 3, '0') AS marker,
            t2.buffer || '   ', t1.id, t1.parent_id, t2.buffer || t1.name
    FROM yourTable t1
    INNER JOIN cte t2
        ON t1.parent_id = t2.id
)

SELECT name FROM cte ORDER BY marker;

enter image description here

Demo

The basic idea here is to build path strings which track the complete path from every node going to back its root (the root being given by a node whose parent_id is NULL). Then, we simply do a single ORDER BY on this path to generate the order you want.

Upvotes: 6

madflow
madflow

Reputation: 8490

You have not provided the queries you already came up with. But - as far as I can see you want a recursive tree structure.

https://www.db-fiddle.com/f/og5HZDHBhBRmP1cDnqgCBB/1

CREATE TABLE rooms (
  id INTEGER, parent_id INTEGER, name TEXT
);

INSERT INTO rooms VALUES
(1, null, '123 Barclay St'),
(2, 1, 'Floor 1'),
(3, 1, 'Floor 2'),
(4, 1, 'Floor 3'),
(5, 2, 'Hall 1'),
(6, 2, 'Room 1'),
(7, 2, 'Room 2'),
(8, 3, 'Room 3'),
(9, null, '10 Thames St');

And the query:

WITH RECURSIVE tree AS (
    SELECT
        rooms.id,
        rooms.parent_id,
        rooms.name
    FROM
        rooms
    WHERE
        parent_id IS NULL
    UNION ALL
    SELECT
        rooms.id,
        rooms.parent_id,
        rooms.name
    FROM
        tree
        JOIN rooms ON rooms.parent_id = tree.id
)
SELECT
    *
FROM
    tree;

https://www.postgresql.org/docs/current/static/queries-with.html

Upvotes: 5

Related Questions