From: | Renato De Giovanni <rdg(at)viafractal(dot)com(dot)br> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Nested set model |
Date: | 2001-08-18 01:00:40 |
Message-ID: | 3B7DBE38.9F1FF640@viafractal.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)viafractal(dot)com(dot)br
From | Date | Subject | |
---|---|---|---|
Next Message | Bill | 2001-08-18 05:23:33 | Function define question |
Previous Message | Stephen Patterson | 2001-08-17 18:23:17 | Finding table constraints |