Nested Sets and Delete/Exchange?
Date: Sun, 21 Sep 2003 19:09:37 +0200
Message-ID: <bkkm0l$ogc$02$1_at_news.t-online.com>
Hi all,
I use a simple nested set to repesent a hierarchical menu in SQL. I've read Joe Celkos article about trees at
http://www.intelligententerprise.com/001020/celko.shtml
and my simple menu table (in MySql) contains the fields id(bigint), menuname(varchar), left(int), right(int). So it's easy to build up a structure like this:
A(1,8)
| \
B(2,3) C(4,7)
|
D(5,6)
Unfortunately the reorganization of the tree is more complex and I can not find answers to my two questions on the net:
- How to renumber the values for left and right after the deletion of an element? When I delete element C(4,7) in the tree, D and A have to change to D(4,5) and A(1,6). How to formulate this in SQL?
- How to exchange two elements and their subtrees? After swapping elements B and C (their menu names) the tree should be structured like this:
A(1,8)
| \
C(2,5) B(6,7)
|
The problem are the subtrees (D) which can hold a different number of elements. I tried to save the subtrees in a temporary table, delete them from the tree and then reinsert them under their swapped parents, but the copy/delete/reinsert takes a lot of SQL code. Is there any easier way to swap elements?
Kind regards,
Oliver
Received on Sun Sep 21 2003 - 19:09:37 CEST