I continue to compare the nested set model with the Oracle hierarchical model from where I left off in Nested Sets in Oracle (5).
I now have a way to do:
Now let’s produce a query that can retrieve our category tree, along with a product count for each category:
Aggregate Functions in a Nested Set (cont.)
I have created a function to return the number of rows that are joined between the nodes of a sub-tree of categories with the PRODUCT table. This is a variation of the sub-tree query shown in Depth of a Sub-Tree.
CREATE OR REPLACE FUNCTION num_of_products_for_category( p_category_name IN VARCHAR2 ) RETURN NUMBER AS l_product_count NUMBER NOT NULL := 0; BEGIN SELECT COUNT(*) INTO l_product_count FROM ( SELECT category_id FROM category START WITH name = p_category_name CONNECT BY parent = PRIOR category_id ) cat INNER JOIN product USING ( category_id ); RETURN l_product_count; END num_of_products_for_category; /
The query to reproduce the MySQL example is:
SELECT name, num_of_products_for_category( name ) AS category_count FROM category /
And the output is:
NAME NUM_OF_PRODUCTS_FOR_CATEGORY(NAME) -------------------- ---------------------------------- 2 WAY RADIOS 1 CD PLAYERS 2 ELECTRONICS 10 FLASH 1 LCD 1 MP3 PLAYERS 2 PLASMA 2 PORTABLE ELECTRONICS 5 TELEVISIONS 5 TUBE 2 10 rows selected.
I do not know how to do a purely SQL version as everytime I unroll the function into the query, I get an error about the correlated column in the START WITH clause.
Adding New Nodes
This is where the Oracle implementation powers ahead of the Nested Sets model.
Insert the new node with the following DML:
INSERT INTO category ( name, parent ) VALUES( 'GAME CONSOLES', get_category_id( 'ELECTRONICS' ) ); COMMIT;
There is no shuffling of pointers to add a new node. From the original article, I get the impression that the order of the siblings is important. Don’t know why?
If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let’s add a new FRS node below the 2 WAY RADIOS node:
INSERT INTO category ( name, parent ) VALUES( 'FRS', get_category_id( '2 WAY RADIOS' ) ); COMMIT;
Running the query Retrieving a Full Tree
NAME ---------------------- ELECTRONICS . PORTABLE ELECTRONICS . . MP3 PLAYERS . . . FLASH . . CD PLAYERS . . 2 WAY RADIOS . . . FRS . TELEVISIONS . . LCD . . TUBE . . PLASMA . GAME CONSOLES 12 rows selected.
To delete a leaf, use:
DELETE FROM category WHERE name = 'GAME CONSOLES'; COMMIT;
This is straight forward because of the uniqueness constraint on the NAME column.
To delete a sub-tree, use:
DELETE FROM product WHERE category_id IN ( SELECT category_id FROM category START WITH name = 'MP3 PLAYERS' CONNECT BY parent = PRIOR category_id ) ; DELETE FROM category WHERE category_id IN ( SELECT category_id FROM category START WITH name = 'MP3 PLAYERS' CONNECT BY parent = PRIOR category_id ) ; COMMIT;
This is complicated because of the RI between PRODUCT and CATEGORY (something you did have to worry about in MySQL 4).
The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent:
UPDATE category SET parent = (SELECT parent FROM category WHERE name = 'PORTABLE ELECTRONICS' ) WHERE parent = (SELECT category_id FROM category WHERE name = 'PORTABLE ELECTRONICS' ) ; DELETE FROM category WHERE name = 'PORTABLE ELECTRONICS'; COMMIT;
If the function GET_CATEGORY_ID is used, then the following message will appear:
ERROR at line 3: ORA-04091: table MBRUCE.CATEGORY is mutating, trigger/function may not see it ORA-06512: at "MBRUCE.GET_CATEGORY_ID", line 39
And the resulting tree is:
NAME ---------------- ELECTRONICS . CD PLAYERS . 2 WAY RADIOS . . FRS . TELEVISIONS . . LCD . . TUBE . . PLASMA 8 rows selected.
I hope I have convinced you that nested sets are a bad idea. The manipulation of pointers is just too error-prone.
Oracle’s hierarchical queries are mostly satisfactory. There were a few cases where the Oracle query was more complicated and one (1) case where I had to resort to using a PL/SQL function.