Imagine the following table. First column (ID) is the Primary Key. There is a reflexive relationship between the 1st column and the 3rd (notice the colouring of the letters). Basically, Parent_ID mentions the parent of a particular ID in a hierarchical fashion representing a folder tree. So assuming this is in Oracle DB, how can we get the file path?
| ID | NAME | PARENT_ID |
| 1 | Root | 0 |
| 2 | First level Folder 1 | 1 |
| 3 | First level Folder 2 | 1 |
| 4 | Second level Folder | 2 |
There’s a neat little concept in Oracle to do this called CONNECT_BY. Lets look at the answer first,
SELECT path FROM (SELECT ID , PARENT_ID , FILE_NAME , SYS_CONNECT_BY_PATH(FILE_NAME, '/') AS path, RANK() OVER (ORDER BY SYS_CONNECT_BY_PATH(FILE_NAME, '/') DESC) AS rank FROM files WHERE id=? CONNECT BY PRIOR id = parent_id ) WHERE rank=1;The output for the above table is as follows,
| PATH |
| /Root/First level Folder 1/Second level Folder |
Now the explanation,
- CONNECT BY PRIOR Operator – Made to use in such reflexive hierarchical situations. What it does is, it identifies this relationship and arranges records based on that arranges the records.
- SYS_CONNECT_BY_PATH(FILE_NAME, ‘/’) – Uses the CONNECT BY feature and then uses column FILE_NAME to make a relationship between the child records and the parent records.
- RANK() OVER (ORDER BY LENGTH([<path>] DESC)) - This is a bit of a tricky one.
- First of all, this orders the paths in a descending order of lengths of paths. The reason to do this is that is ex: for ID-4, it shows all paths: /Root/, /Root/First level Folder 1/ and /Root/First level Folder 1/Second level Folder, so we need to find the longest path.
- Secondly, we use the uses the RANK() method to rank the paths on their length in the descending way to find the longest path. As a result, the longest path would have the rank 1. In the outer query, the WHERE clause makes sure that unwanted paths are not shown to us
If more explanation is needed, please leave a comment and I shall get back to you
