A closure table is simply a table that maintains the "transitive closure" of the parent-child relationships in the base table. So, let's say you're modelling a directory structure, and you have a "directory" table, with a foreign key "parent_dir" pointing to each row's parent directory.With this structure, you can only query direct (depth 1) relationships, but by adding a "closure" table with fields for "parent", "child", and "depth", you can represent the hierarchy to whatever depth is present. So, if directory C is a child of directory B, and directory B is a child of A, then the base table would look like this:
| id | parent_dir | name |
|---|---|---|
| 1 | 0 | A |
| 2 | 1 | B |
| 3 | 2 | C |
And the closure table would look like this:
| parent | child | depth |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 1 | 2 | 1 |
| 2 | 3 | 1 |
| 1 | 3 | 2 |
For more information: http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html
The Structure
There are two tables needed for this structure:
CREATE TABLE IF NOT EXISTS `Module` (
`module_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`testsuite_id` int(10) unsigned NOT NULL,
`module_name` varchar(64) NOT NULL DEFAULT '',
`module_description` longtext,
`parentModule_id` varchar(64) DEFAULT '',
PRIMARY KEY (`module_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `Module_Clousure` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parentModule_id` int(10) unsigned NOT NULL,
`child_id` int(10) unsigned NOT NULL,
`depth` int(10) unsigned NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
And the query for the insertion in Module_Clousure table should be:
$insMod = db_query("INSERT INTO Module_Clousure (parentModule_id, child_id, depth,date) VALUES (%d, %d,%d,NOW())", $id,$id,0);
$insMod = db_query("INSERT INTO Module_Clousure (parentModule_id, child_id, depth,date)
SELECT p.parentModule_id, c.child_id, p.depth+c.depth+1, NOW()
FROM Module_Clousure as p, Module_Clousure as c
WHERE p.child_id=%d and c.parentModule_id=%d",$parent,$id);
Tu build the directory tree in php:
1. Make a consult to the "Module" table to retrieve all the directories with no parent. Let's call them the king modules.
2. For every king module retrieve the depth of the tree.
3. The resulting depth of the tree allows us to build a query to travel around the tree:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM NMTM_Module AS t1 LEFT JOIN NMTM_Module AS t2 ON t2.parent = t1.module_id LEFT JOIN NMTM_Module AS t3 ON t3.parent = t2.module_id LEFT JOIN NMTM_Module AS t4 ON t4.parent = t3.module_id WHERE t1.name = '';
Continuing until t(n), where n is the depth of the tree. With this the resulting query we will have something like this:
level1 level2 level3 level4
1 3 4
1 2 5 6
Meaning node 1 have two children (3 and 2). Node 3 have one children (node 4). Node 2 have one children (node 5) and node 5 have one children (node 6). If node 5 have two children for example another row would appear next:
level1 level2 level3 level4
1 3 4
1 2 5 6
1 2 5 7
In this way with PHP we iterate trough every row and print the child in the corresponding order. To assign margins to the corresponding child I store the depth in a html tag and with a function in jquery/javascript I assign a certain margin. The function looks like this:
$("*[class^='group"+id+"']").css('margin-left', "0");
$("*[class^='group"+id+"']").each(function() {
var depth = this.getAttribute('data-depth');
var move = (depth*30)-n;
$(this).css('margin-left', move+"px");
});
Where "id" is the id of the node. Now for every node I assign an "id" connecting the "id" of the parent and the "id" of the child. So for example, if node 1 is parent of node 2, and node 2 is parent of node 4, the resulting id class for node 4 would be: id="124".
"n" is the depth of the first node parent to show. For example if I have this:
level1 level2 level3 level4
1 2 5 7
1 2 5 6
But I select node 5, meaning I only want to see node 5 with its child nodes (and not 1 or 2 nodes), then "n" is the depth of node 5 (in this case n would be 3). So "move" for the first node will be 0, and for the second node (7) would be its depth less the parent one, etc.
I could have assigned the margin with the first iteration, but I have to build something that allowed me to filter the directories whenever I want (this mean to show only the children of node 1, or only the children of node 5, etc) So, to do this dynamically was a better option.
