Create Directory tree

To manage hierarchies in SQL I used a Closure table. A closure table allows you to "find children of X, to depth N", with a join. To add or delete rows from this table you only need one SQL query.

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.