Getting Breadcrumbs in Postgres

Question

I have a table that represents a hierarchy by referencing itself.

create table nodes (
  id integer primary key,
  parent_id integer references nodes (id),
  name varchar(255)
);

Given a specific node, I would like to find all of its parents in order, as breadcrumbs. For example, given this data:

insert into nodes (id,parent_id,name) values 
(1,null,'Root'),
(2,1,'Left'),
(3,1,'Right'),
(4,2,'LeftLeft'),
(5,2,'LeftRight'),
(6,5,'LeftRightLeft');

If I wanted to start at id=5 I would expect the result to be:

id | depth | name
-- | ----- | ----
1  | 0     | 'Root'
2  | 1     | 'Left'
5  | 2     | 'LeftRight'

I don't care if the depth column is present, but I included it for clarity, to show that there should only be one result for each depth and that results should be in order of depth. I don't care if it's ascending or descending. The purpose of this is to be able to print out some breadcrumbs that look like this:

(1)Root \ (2)Left \ (5)LeftRight

mu is too short · Accepted Answer · 2014-01-22 23:15:01Z

4

The basic recursive query would look like this:

with recursive tree(id, name, parent_id) as (
    select n.id, n.name, n.parent_id
    from nodes n
    where n.id = 5
    union all
    select n.id, n.name, n.parent_id
    from nodes n
    join tree t on (n.id = t.parent_id)
)
select *
from tree;

Demo: http://sqlfiddle.com/#!15/713f8/1

That will give you everything need to rebuild the path from id = 5 back to the root.

answered Jan 22, 2014 at 23:15

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Retallack Over a year ago

Does it not matter that you labeled both selects "n" there? I'd think n.id would be ambiguous, unless sql is implicitly shadowing the prior definition of n.

mu is too short Over a year ago

The table alias won't cross the UNION ALL boundary between the queries; in A UNION B, the A and B queries have their own namespaces.

Collectives™ on Stack Overflow

Getting Breadcrumbs in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related