27

I have a table denoting parent-child relations. The relations can go n-level deep.

I have created a sample table using the following query:

CREATE SEQUENCE relations_rel_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
CREATE TABLE relations(
    rel_id bigint DEFAULT nextval('relations_rel_id_seq'::regclass) NOT NULL PRIMARY KEY,
    rel_name text,
    rel_display text,
    rel_parent bigint
);

SQLFiddle

I need to query the table and display the parent-child relations hierarchically. I'm still not getting an idea regarding how to query n-level deep using sql query.

For the sqlfiddle eg, the expected hierarchy of output:

rel1
    rel11
        rel111
        rel112
            rel1121
rel2
    rel21
        rel211
        rel212

N.B: The value n, in n-level is unknown.

DB Design:

Is there any better way such a relation can be expressed in the database for easy querying.?

saji89
  • 2,093
  • 4
  • 27
  • 49
  • 2
    You're probably looking for WITH RECURSIVE, there's an example [over here](http://stackoverflow.com/q/14491526/479863). – mu is too short Feb 02 '13 at 08:23
  • @muistooshort, Thanks for the suggestion. 'll look into it. Do you have any suggestion for alternative db design, that can make it easier for querying and retrieving relations in such cases? – saji89 Feb 02 '13 at 08:44
  • @saji89 Read [`this`](http://www.postgresql.org/docs/current/static/queries-with.html) page of the manual. It describes `WITH RECURSIVE` queries you need. – Ihor Romanchenko Feb 02 '13 at 12:56
  • @saji89 BTW It is good to mention your RDBMS version. Recursive queries aren't available in PostgreSQL below 8.4. – Ihor Romanchenko Feb 02 '13 at 12:58
  • 1
    I find the `rel_parent` structure that you have to be the most natural design for trees; it makes sense and, given WITH RECURSIVE, is easy to work with for all the usual operations. You could look at "nested sets" and "materialized paths" I suppose. – mu is too short Feb 02 '13 at 18:33

1 Answers1

53

With Postgres you can use a recursive common table expression:

with recursive rel_tree as (
   select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info
   from relations 
   where rel_parent is null
   union all
   select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id
   from relations c
     join rel_tree p on c.rel_parent = p.rel_id
)
select rel_id, rel_name
from rel_tree
order by path_info;

SQLFiddle based on your example: http://sqlfiddle.com/#!11/59319/19

(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)

  • Great answer, exactly what the OP is looking for! But there is a little mistake in the `rpad(..)` part, changed it to `rpad('', p.level * 2, ' ')`. Again space is replaced with '_' on [SQL Fiddle](http://sqlfiddle.com/#!11/59319/38). – tscho Feb 02 '13 at 22:04
  • 1
    Just checked the [docs](http://www.postgresql.org/docs/9.2/static/functions-string.html): space is the default fill character (optional 3rd parameter), so I had to use the '_' fill character explicitly on SQLFiddle. – tscho Feb 02 '13 at 22:43
  • +1, Wow. `Postgresql` doesn't cease to surprise me. Thankyou. – saji89 Feb 03 '13 at 04:48
  • For a moment I was at a loss as to how to use a normal `with` expression together with `with recursive`. Seems like you're supposed to put the `with` expression inside the `with recursive`. – mkataja May 13 '15 at 11:59
  • 3
    @mkataja: no, you just add it. `with recursive first_cte (...), second_cte (...) select * from second_cte`. The `recursive` keyword is only required *once* together with the initial `with` keyword - regardless which CTE is recursive. –  May 13 '15 at 12:03
  • Heh, that makes sense. Somehow I thought the `recursive` keyword needs to be together with the actual recursive CTE(s). – mkataja May 13 '15 at 12:22