I am having some difficulty wrapping my mind around SQL closure tables, and would like some assistance in understanding some of the examples I have found.
Lets say 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 tree structure should effectively be this:
id
| - 1
| | - 2
| | - 3
| - 4
For ease of querying trees (such as finding all of the descendants of a specific id), I have a table called sample_items_closure using the method described by Bill Karwin in this excellent SO post. I also use an optional path_length column for querying the immediate child or parent when needed. If I understand 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
Every row in sample_items now has an entry in the sample_items_closure table for both itself and all of it's ancestors. Everything makes sense so far.
While studying other closure table examples, however, I came across one that adds an additional ancestor for each row that links to the root level (ancestor_id 0) and has a path length of 0. Using the same data I have above, this is what the closure table would look like:
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 give better context, here is a select query used on that site, modified to fit 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 #1:
Why would an additional row be added linking each descendant to the root level (id 0)?
Question #2:
Why would path_length be 0 for these entries, instead of being the previous ancestor's 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 include an adjacency list (the parent_id column for sample_items in my example) when the full structure of the tree is already expressed in the closure table?
0 3 0comes from in your closure table. It's not in that second link you give. Maybe you misinterpreted it? – Cambraiclosure.ancestor_id= 0` This example comes from times when there was no object-query-builder. #A2. length from 0 to 1 is 0 because is couple root elements. #BQA: To get only part tree. – Hardison