Now that we have explored how to report on the adjacency list model using Oracle as described in Nested Sets in Oracle (3), I now move onto the nested sets part of Managing Hierarchical Data in MySQL.
Limitations of the Adjacency List Model
Since the author does give an examples of the inadequacies of the model, I will not address them here but show how the current model is adequate given the power of Oracle 10G SQL.
The Nested Set Model
I am not going to change the CATEGORY table as I had created it in Nested Sets in Oracle (1) because it is adequate for the purposes of demonstration.
As the author tries to do, the construction of the left and right pointers for a binary tree is error-prone. I think my data loading script in Nested Sets in Oracle (2) is more intuitive but it is still inelegant. The idea is to let the computer work out the internal links rather than the user.
Retrieving a Full Tree
The query given in Nested Sets in Oracle (3) is unchanged. There is no need for a join, sorting, or for the specification of the root node by name in my version of the query.
Finding all the Leaf Nodes
The query given in the MySQL article is much simpler than the one I produced in Nested Sets in Oracle (3).
Retrieving a Single Path
There is no easy way to produce the same output in Oracle. One SQL solution is:
WITH tree_path AS ( SELECT SYS_CONNECT_BY_PATH( name, '/' ) || '/' AS path FROM category WHERE name = 'FLASH' START WITH parent IS NULL CONNECT BY parent = PRIOR category_id ) SELECT name FROM category WHERE INSTR( (SELECT path FROM tree_path), '/' || name || '/' ) > 0 START WITH parent IS NULL CONNECT BY parent = PRIOR category_id /
I use the tree_path subquery to return a single row that contains the path expressed as concatenated list of node names delimited by ‘/’. I then walk the tree again checking the name of each node against the returned path. This is definitely ugly.
The output is:
NAME -------------------- ELECTRONICS PORTABLE ELECTRONICS MP3 PLAYERS FLASH
Finding the Depth of the Nodes
This is trivial in Oracle:
SELECT name, LEVEL - 1 AS depth FROM category START WITH parent IS NULL CONNECT BY parent = PRIOR category_id /
The output is:
NAME DEPTH -------------------- ---------- ELECTRONICS 0 PORTABLE ELECTRONICS 1 MP3 PLAYERS 2 FLASH 3 CD PLAYERS 2 2 WAY RADIOS 2 TELEVISIONS 1 LCD 2 TUBE 2 PLASMA 2 10 rows selected.