2 Replies Latest reply: Nov 21, 2012 12:58 PM by user12200443 RSS

    advanced: (tree / node deletion) How to delete from leaf node to parent in

    user12200443
      I have a tree represented in the DB (think more folder hiea than a tree, no concept of left node right node).

      Some of the nested folders are orphaned so I need to find them and delete them.

      Two tables support this like:

      table name: folders
           column: id
           column: folder_name
           column: type               (can be of type 'root' or 'folder')
           
      table name: parent_child
           column: parent_id          <= is a folder.id
           column: child_id          <= is a folder.id
           
      To find the level 1 folders that are orphaned, I can do:

           $select * from folders f where type='folder' and id not in (select parent_id from parent_child);
           
           this is the first level folders that are orphaned. These also have children (with constraints).
           
           so I need to get a list of the first level folders that are orphaned and delete from the leaf
           nodes up to the folder that is orphaned.
           
           Is this possible to do?
           
      ---
      Visual:
           - root1
                - folder1a <= hiearchy of folders
           - root2          <= this root should exist but does not so no entry in 'parent_child' for all children
                - folder2a
                - folder2b     <= should exist but does not, so no entry in 'parent_child' for all children
                     - folder2ba          <= this folder and all children should be deleted
                          - folder2baa
                          - folder2bab
                          - folder2bac
                     - folder2bb          <= this folder and all children should be deleted
                     - folder2bc          <= this folder and all children should be deleted
                - folder2c
           - root3               <= hiearchy of folders