Unfolding tree structure using nested sets

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Unfolding tree structure using nested sets

Post by visionmaster »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello together,

I would really appreciate any helpful tips!

Using nested sets I want to implement an unfolding tree sructure.

-Choosing a main branch (node) shows the branches belonging to this main branch.
-Branches that in turn hold further branches can be unfolded by click of a plus symbol.
-A click on the displayed - symbol closes the branch.

-So, a plus expands a level, a click on a minus should go one level higher.

-If a user opens a branch/node, the children nodes of the next level of the choosen node should be displayed.

My problem was that when clicking on a plus symbol of e.g branch b, I opened the children nodes of branch b AND d.

So my question is, how do I display the children nodes of a specific node and explicitely the children nodes
of the next level. In other words how do I display the path from the chosen main branch to its children nodes and
all the nodes of a specific level belonging to the children node.

My SQL-query for opening the children nodes of a node looks like this:

[syntax="sql"]SELECT o.branche_name, o.branche_id, o.branche_nace, o.branche_name, floor( (
o.branche_rgt - o.branche_lft
) /2 ) AS children, COUNT( p.branche_id ) AS
LEVEL FROM branchen_nace AS n, branchen_nace AS p, branchen_nace AS o
WHERE o.branche_lft
BETWEEN p.branche_lft
AND p.branche_rgt
AND o.branche_lft
BETWEEN n.branche_lft
AND n.branche_rgt
AND n.branche_id =570
GROUP BY o.branche_lft
HAVING LEVEL <=4
ORDER BY o.branche_lft
------

1. Display all branches under the main branch machine

machine (level 2)
|
|___ a
|
|___ b +
|
|___ c
|
|___ d +

SQL-statement for 1:

Code: Select all

SELECT o.branche_name, o.branche_id, o.branche_nace, o.branche_name, floor( (
o.branche_rgt - o.branche_lft
) /2 ) AS children, COUNT( p.branche_id ) AS
LEVEL FROM branchen_nace AS n, branchen_nace AS p, branchen_nace AS o
WHERE o.branche_lft
BETWEEN p.branche_lft
AND p.branche_rgt
AND o.branche_lft
BETWEEN n.branche_lft
AND n.branche_rgt
AND n.branche_id =570
GROUP BY o.branche_lft
HAVING LEVEL =2
OR LEVEL =3
ORDER BY o.branche_lft


2. User unfolds branch b
machine
|
|___ a
|
|___ b -
|
|________ b1
|
|________ b2
|
|___ c
|
|___ d +



2.INCORRECT User unfolds branch b. The children branches of d are also displayed, which ist incorrect.
Since the user just wanted to unfold branch b.

machine
|
|___ a
|
|___ b -
|
|________ b1
|
|________ b2
|
|___ c
|
|___ d -
|
|________ d1
|
|________ d2

SQL-statement for 2.INCORRECT is:

Code: Select all

SELECT o.branche_name, o.branche_id, o.branche_nace, o.branche_name, floor( (
o.branche_rgt - o.branche_lft
) /2 ) AS children, COUNT( p.branche_id ) AS
LEVEL FROM branchen_nace AS n, branchen_nace AS p, branchen_nace AS o
WHERE o.branche_lft
BETWEEN p.branche_lft
AND p.branche_rgt
AND o.branche_lft
BETWEEN n.branche_lft
AND n.branche_rgt
AND n.branche_id =570
GROUP BY o.branche_lft
HAVING LEVEL <=4
ORDER BY o.branche_lft 
3. User unfolds branch d

machine
|
|___ a
|
|___ b +
|
|___ c
|
|___ d -
|
|________ d1
|
|________ d2
|
|________ d3


4. User unfolds underbranch d2

machine
|
|___ a
|
|___ b +
|
|___ c
|
|___ d -
|
|________ d1
|
|________ d2 -
|
|______________ d21
|
|______________ d22
|
|________ d3


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Post Reply