lundi 26 janvier 2015

Recurse path from leaf to root?


I have a table structure (in an Oracle 11g database [11.2.0.3.0]) that maps out a folder hierarchy for employee records that looks like this (note: I have to start at the leaf node and recurse up to the root node in each case):


TBL_FOLDER FOLDERID, NUMBER FOLDERNAME, VARCHAR2 FOLDERTYPE, NUMBER


TBL_FOLDER_HIERARCHY FOLDERID, NUMBER PARENTFOLDERID, NUMBER PARENTFOLDERTYPE, NUMBER


To get the name and ID of the next folder up, starting at any particular node (below I've used a starting FOLDERID of 10000), I would do something like:


SELECT F1.FOLDERNAME, F1.FOLDERID FROM TBL_FOLDER F INNER JOIN TBL_FOLDER_HIERARCHY FH ON F.FOLDERID = FH.FOLDERID INNER JOIN TBL_FOLDER F1 ON FH.PARENTFOLDERID = F1.FOLDERID WHERE FH.PARENTFOLDERTYPE = 1 AND F.FOLDERTYPE = 1 AND F1.FOLDERTYPE = 1 AND FH.FOLDERID = 10000;


I'm trying to work out how to recurse up through the nodes to get a string equating to the path for each employee (I don't mind if this is 'reversed', due to starting at the leaf and working back to the root). I am able to do this in C# code, using a method that calls itself for each level, but when doing this for a lot of employees, with a hierarchy that can be quite deep, this means a lot of queries.


As an additional criterion, the 'root node' for all employees has a FOLDERID = 999 and a FOLDERTYPE = 1. There are folder entries above this, but they become meaningless for this process.


Is it possible to recurse the above using a single query in Oracle 11g? Or should I look at something like a function that calls itself, similar to the C# method approach I used?


I've experimented with queries in which you create the number of self joins required to represent the depth to the root, but this seems inefficient, as I'd like to be able to provide a list of employees to the query from any path structure, and return their path, without having to build a UNIONed query for each possible level in the overall tree.





Aucun commentaire:

Enregistrer un commentaire