codeIgniter closure table model

Question

This is the class I am working with: https://gist.github.com/2174233

I have two tables: one table contains users and the other - closures.

When I use get_children() method (let's say from the root or another parent), it show only the first level of the hierarchy. What values should I store in lvl column?

This is the definition of closure table, that I have:

 CREATE TABLE closures (
     id         INT(11) NOT NULL AUTO_INCREMENT,
     ancestor   INT(11) NOT NULL,
     descendant INT(11) NOT NULL,
     lvl        INT(11) NOT NULL,

     PRIMARY KEY (id)
 )

Here's an example of data that i store in the closure table:

INSERT INTO `closures` (`id`, `ancestor`, `descendant`, `lvl`) 
VALUES (1, 1, 20, 0),
       (4, 20, 26, 0),
       (5, 26, 25, 0);

The last is the lvl column, but I don't know, what values goes there. Can you give me an advice of how to use it?

The structure that i have to store has 3 levels : root -> 20 node -> 26 node, but it gives me only the first level children that is the 20 node.

http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html - i have readed this article, so there is a different use than in this code model, thats why i am asking how to use it to render many levels, not the first only... — Mister PHP, Jul 07 '12 at 22:09
Your data do not look like a closure mdel. More like a adjacency list model. — ypercubeᵀᴹ, Jul 07 '12 at 22:09
by now i can only render the first level of children nodes, so how can i render one more level, or 2,3 more levels? in the comments of the function its said that: Example to generate nested tree: * * $data = $this->get_children(1, TRUE, FALSE, TRUE); * print_r($data); but i only get the first level, do you have an idea of how to use it? — Mister PHP, Jul 07 '12 at 22:21

score 3 · Accepted Answer · edited Jul 08 '12 at 09:34

I would recommend for you to pick up the SQL Antipatterns book. The second chapter feature closure tables as one of recommended ways for implementing category trees.

That said. Looks like your closure table is a bit strange. There is no point in id column there. Instead you should have a composite primary key, made from unique pairs of ancestor and descendant value.

And you have not inserted the nodes themselves .. only the connection between two different nodes. Maybe reading "Rendering Trees with Closure Tables" could shine some light on the subject.

At a guess the INSERT statement should look like this (at least that my conclusion):

INSERT INTO closures(ancestor, descendant, lvl) 
VALUES (1,  1,  null),
       (20, 20, null),
       (26, 26, null),
       (28, 28, null),
       (1,  20, 1),
       (20, 26, 2),
       (26, 25, 3);

What you have to understand is that closure tables are not storing a tree. Instead, the data structure you are working with, is directional graph. Something like this:

enter image description here

As you can see, this graph has three root nodes: 3, 5 and 7. Also, it is very important to note, that node 10 is a different levels of depth, depending on from which root node you begin.

It would be defined with two closures: [3,10,1] and [11,10,2]. Meaning, that the connection from 11th node would put it a level two, while starting from 3rd node, it's a first level item.

Thing is, when you are using closure tables each category can have multiple parent categories each at different level of depth.

Addition (by @ypercube):

My understanding of the "level" or "depth" column is that it stores the "distance" (steps needed to go) from ancestor to descedant. It's not an absolute level of a node and thus closure tables can be used to store more complex than trees graphs. You may even have multiple paths from an ancestor to a descendant, each one (path) with different steps.

Additionally, the Nulls should be 0 and a few more rows are needed.

So, the data would be:

INSERT INTO closures(ancestor, descendant, lvl) 
VALUES ( 1,  1,  0), (20, 20, 0), (26, 26, 0), (25, 25, 0),
                     ( 1, 20, 1), (20, 26, 1), (26, 25, 1),
                                  ( 1, 26, 2), (20, 25, 2),
                                               ( 1, 25, 3) ;

I think that those Nulls should be 0 (easier to do calculations that way) and your level numbers are wrong (the names "level" and "depth" I've seen used, add to confusion. It's more like the distance from ancestor to descedant). And 3 more rows are needed: `(1, 26, 2)`, `(1, 25, 3)` and `(20, 25, 2)` — ypercubeᵀᴹ, Jul 08 '12 at 09:22
Actually you are right about the missing entries (though I would still use `null` for the root node .. there was some reason, which I cannot recall, why `null` values were useful there). Anyway, tanks .. that's one gets for writing answers in the middle night. — tereško, Jul 08 '12 at 15:24

codeIgniter closure table model

1 Answers1