I had difficulty transferring my mind to SQL closing tables and would like some help in understanding some of the examples I found.
Suppose I have a table called sample_items with the following hierarchical data:
id name parent_id 1 'Root level item #1' 0 2 'Child of ID 1' 1 3 'Child of ID 2' 2 4 'Root level item #2' 0
The effective tree structure should be as follows:
id | - 1 | | - 2 | | - 3 | - 4
For the convenience of querying trees (for example, to search for all descendants of a specific id), I have a table called sample_items_closure using the method described by Bill Carwin in this excellent SO post . I also use the optional path_length column to query the immediate child or parent if necessary. If I understood this method correctly, my closure table data would look like this:
ancestor_id descendant_id path_length 1 1 0 2 2 0 1 2 1 3 3 0 2 3 1 1 3 2 4 4 0
Each row in sample_items now has an entry in the sample_items_closure table sample_items_closure both itself and all its ancestors. Everything makes sense so far.
However, when studying other examples of the closing table, I came across the fact that it adds an additional ancestor for each row that refers to the root level (ancestor_id 0) and has a path length of 0. Using the same data as above, it will look like a closing table:
ancestor_id descendant_id path_length 1 1 0 0 1 0 2 2 0 1 2 1 0 2 0 3 3 0 2 3 1 1 3 2 0 3 0 4 4 0 0 4 0
To provide a better context, here is the selection request used on this site, modified according to my examples:
SELECT `id`,`parent_id` FROM `sample_items` `items` JOIN `sample_items_closure` `closure` ON `items`.`id` = `closure`.`descendant_id` WHERE `closure`.`ancestor_id` = 2
I have two questions related to this method:
Question number 1:
Why is an extra line added that connects each descendant with the root level (id 0)?
Question number 2:
Why is path_length going to be 0 for these records, and not the previous ancestor of path_length + 1? For example:
ancestor_id descendant_id path_length 1 1 0 0 1 1 2 2 0 1 2 1 0 2 2 3 3 0 2 3 1 1 3 2 0 3 3 4 4 0 0 4 1
Bonus question : Why do some examples still have an adjacency list ( parent_id column for sample_items in my example) when the full tree structure is already expressed in the closure table?