Practical .NET

Back in November, I posted about a Hierarchical Data scheme that I was working on for a category tree.  The article I mentioned came in handy.

Well, I'm working on another project (this time, it's a project for my wife), but wanted the possibility of multiple users defining their own hierarchical tree, but store them all in the same table.  This is using the Nested Set model.

The table will look like:

+-------------+---------------+
| Name        | char(255)     |
| lft         | int           |
| rgt         | int           |
| Owner       | char(255)     |
+-------------+---------------+

The owner field will keep track of the multiple users.

+-------------+---+---+-------+
| Name        |lft|rgt|owner  |
+-------------+---+---+-------+
| Root        | 1 | 4 | kyle  |
| Category1   | 2 | 3 | kyle  |
| Root        | 1 | 4 | kyle1 |
| Category12  | 2 | 3 | kyle1 |
+-------------+---+---+-------+

Now, the query to pull back the data, with the depths...

SELECT node.*, (COUNT(parent.name) - 1) AS depth
FROM table1 AS node,
table1 AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

This may bring back all the correct information, but the depths will be screwed up.  I'm not sure of all the technical details behind that, and it's something that I want to learn.  But, I did figure out how to fix this.

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM (select * from table1 where owner = 'kyle') AS node,
(select * from table1 where owner = 'kyle') AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

The calculations are based off of a base table, which is in the first query defined as table1.  This is the entire dataset.  However, if we narrow the data down to only the data requested for each user by defining a select query in the from statement, it "tricks" the join into thinking that the data subset is the table. 

I'm sure this last paragraph was as clear as mud, but you get the point.  With this model, you can do multiple nested data sets inside of one table.  Great for a shared environment.