Store level in addition to hierarchy information

[tehplague]

Your implementation of a closure table is quite interesting and I am currently using it in one of my projects. However, it would be nice if you could add a column for the tree distance to the hierarchy table, i.e. the number of tree hops parent and child are apart.

Immediate children therefore have a distance of 1. This would enable us to efficiently implement some sort of Descendants() function that allows for limiting the depth of the respective tree result. Additionally we would be able to query the immediate children of a certain tree node, simply by employing the distance.

[hazzik]

Hi @tehplague, I'm glad that someone found this useful.

Unfortunately it is not really easy to add level column to the hierarchy table, but you can add the level column to the entry itself.

So, there is two possible ways:

1. Not persisted, calculated at client side

   public class TestTreeEntry : TreeEntry<TestTreeEntry>
   {
      public virtual int Level
      {
          get { return Parent != null ? Parent.Level + 1 : 0; }
      }
   }

2. Persisted, stored at a leaf table.

   public class TestTreeEntry : TreeEntry<TestTreeEntry>
   {
      public virtual int Level { get; set; }
   
      public override TestTreeEntry Parent
      {
          get { return base.Parent; }
          set
          {
              base.Parent = value;
              Level = value == null ? 0 : value.Level + 1;
          }
      }
   }

[hazzik]

Also, could you please add some samples of what you actually want to achieve? @tehplague

[tehplague]

I'm planning to create a fork of your code and will try to prepare a pull request. That might take a bit of time. :)

[tehplague]

OK, maybe let me explain what I want to achieve: I have a database running on SQL Server which I am accessing using NHibernate and expose its data via a JSON REST service based on ASP.NET Web API. Not much of a problem so far. Inside this database there is a large tree structure representing a hierarchy of organizations. I expect this hierarchy to finally consist of several hundreds of tree nodes which is why I am using your tree approach rather than a simple adjacency list.

My main concern is that serializing this data structure would produce a very large result set. However, I most of the time I do not need all data, of course. With your code it is already possible to query all descendants of any tree node, which essentially means taking a whole subtree. What I am trying to do is to walk the tree, beginning from its root node and to stop at a certain depth - let's say at depth 5, for example. It is not possible to filter this information out of your Descendants property because the tree entries do not know about their position in the tree. In theory, your suggested approach of persisting some sort of Level property would solve at least this problem.

It again becomes an issue, when your starting point is not the root node. You then have to take the starting point's Level property, add your desired depth and use the result as the boundary for your query. This of course requires to first load the starting point entity to be able to access its Level information and then another query to load the actual descendants. Not very elegant.

When using Closure tables you would best achieve this by storing the number of tree nodes that lie between the parent and child node for each of your hierarchy entries, i.e. rather some sort of Distance (in terms of a tree distance) rather than a Level property. If Ancestor and Descendant are equal, this distance is obviously zero and it becomes one if the descendant is an immediate child of the ancestor.

Having this information available it is then quite easy to limit the query depth for an arbitrary root node, simply by querying the hierarchy table, looking for entries whose depth does not exceed a certain bound and whose ancestor ID is equal to the root node ID and JOIN'ing the tree entries. Of course, bringing the resulting flat array into the desired real .NET object hierarchy is another concern, but I think you get the point.

[hazzik]

It seems that to achieve it you need to use explicit relationship (instead of many-to-many) or, possible a map. I'm not sure here.