pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Renato De Giovanni <rdg@viafractal.com.br>
To: pgsql-sql@postgresql.org
Subject: Nested set model
Date: Fri, 17 Aug 2001 22:00:40 -0300
Message-ID: <3B7DBE38.9F1FF640@viafractal.com.br> (raw)

Hi,

I'm trying the "nested set model" to handle a tree structure in a
database (reference: http://www.dbmsmag.com/9603d06.html). It has many
advantages if you want to select all nodes above or below a specific
node (recursive calls aren't necessary), but when you want to select
only the first generation under a node, the query I found was a lot more
complex than it would be if using the traditional adjacency model.
Considering this example:

CREATE TABLE skill (
 key        INTEGER NOT NULL,
 name       VARCHAR(50) NOT NULL,
 left_n     INTEGER NOT NULL,
 right_n    INTEGER NOT NULL,
 PRIMARY KEY (key),
 CHECK (left_n > 0 AND right_n > left_n)
);

insert into skill values (1 , 'Skills'     ,  1, 30);
insert into skill values (2 , 'Computing'  ,  2, 29);
insert into skill values (3 , 'Programming',  3, 10);
insert into skill values (4 , 'C++'        ,  4,  5);
insert into skill values (5 , 'Java'       ,  6,  7);
insert into skill values (6 , 'Prolog'     ,  8,  9);
insert into skill values (7 , 'Database'   , 11, 18);
insert into skill values (8 , 'Oracle'     , 12, 13);
insert into skill values (9 , 'PostgreSQL' , 14, 15);
insert into skill values (10, 'Solid'      , 16, 17);
insert into skill values (11, 'Design'     , 19, 28);
insert into skill values (12, 'CorelDraw'  , 20, 21);
insert into skill values (13, 'Illustrator', 22, 23);
insert into skill values (14, 'Photoshop'  , 24, 25);
insert into skill values (15, 'The Gimp'   , 26, 27);

How could we select, for example, only the nodes immediately under
"computing"?

The only way I could do it was using:

select son.key, son.name, son.left_n
from skill son, skill parent
where parent.key = 2
and son.left_n between parent.left_n and parent.right_n
and son.key <> parent.key
and son.key not in
 (select son_descendents.key
 from skill parent, skill son, skill son_descendents
 where parent.key = 2
 and son.left_n between parent.left_n and parent.right_n
 and son.key <> parent.key
 and son_descendents.left_n between son.left_n and son.right_n
 and son.key <> son_descendents.key)
order by son.left_n ;

Isn't there an easier way to achieve this?? With the usual adjacency
model the query would look trivial! Something like:

select son.key, son.name
from skill son
where son.parent = 2 ;

Well, thanks in advance!
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br





Message-ID: <3B7DBE38.9F1FF640@viafractal.com.br>
Permalink:  ../3B7DBE38.9F1FF640@viafractal.com.br/
Also on:    postgresql.org/message-id/3B7DBE38.9F1FF640@viafractal.com.br

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rdg@viafractal.com.br
  Subject: Re: Nested set model
  In-Reply-To: <3B7DBE38.9F1FF640@viafractal.com.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox