|
|
Posted by Evert on 06/15/19 11:25
Anon wrote:
> Im wondering if someone could help me with a recursive style sql command
>
> section = SectionID, parent
>
> I want to be able to delete a row where SectionID=X but also delete any
> section that matches SectionID.
>
> Kinda like the hierarchy of folders on your harddrive.
>
> I want to delete any subfolders of the main folder I am deleting.
>
> Can anyone give me some direction, my SQL isn't up to all that much.
>
> Ive got the following but its more pseudo code rather than something I
> expect to work.
>
>
> DELETE *
> FROM jss_sections
> WHERE Parent=SectionID AND (DELETE * FROM jss_sections WHERE
> SectionID.new=Parent)
>
> TIA
>
> Simon
Hi Anon,
Try the modified preorder tree traversal algorithm, it's ideal for this
problem.
It works in short like this. For every node in the tree you store 2 id's
the left id and the right id.
AA
BB CC
DD EE FF GG
A would get lft id 1
B would get lft id 2
D would get lft id 3
D would get rgt id 4
E lft 5
E rgt 6
B rgt 7
C lft 8
F lft 9
F rgt 10
G lft 11
G rgt 12
C rgt 13
A rgt 14
If you have the id of node D and you want to know its path you can do
SELECT nodename FROM tree WHERE lft<=3 AND rgt>=4 ORDER BY lft
[3 and 4 are D's left and right id's]
You would get as result
D
B
A
If you want to know what node C's children are, you can use the query
SELECT nodename FROM tree WHERE lft>8 AND rgt<13
the result will be the entire subtree of C
Here comes the tricky part:
If you want to insert a new node, all nodes have to shift.
This means if you insert a new subnode of E, every node with a lft or
rgt id higher than E.lft and E.rgt will have to increase by 2.
The exact opposite is also true. When you delete a node, nodes with
higher id's will have to shift back 2.
So, changes in the tree will be a bit slower, but the queries are very
powerfull and easy to construct once you understand the logic. SELECT
queries will also be way faster than the terrible recursive methods [
sometimes you can't avoid them though ]
Anyway.. I can imagine this story was a bit unclear. So google for it if
you didn't uderstand it. I can give you some SQL examples if you drop me
a line.
Evert
Navigation:
[Reply to this message]
|