Previewing Sub-levels
One other common feature of some web directories is to not only list the direct children of the node we're looking at - but also revealing the direct children of those nodes (ie two depths below the current node). Thanks to our lineage and depth columns, with a bit of thought it's easy to return a list of child nodes up to a particular depth.
CREATE PROCEDURE dfTreeGetSubChildren ( @id INT, @depth INT ) AS
SELECT t.* FROM dfTree AS children INNER JOIN dfTree AS actualNode
ON children.lineage LIKE actualNode.lineage + '_%'
WHERE actualNode.id = @id AND children.depth - actualNode.depth <= @depth
ORDER BY children.lineage, children.name
The inner join condition (ON children.lineage LIKE actualNode.lineage + '_%') ensures that any nodes we are returning is a child of the actualNode. The underscore (which matches at least one character) ensures that we don't return the node itself. The WHERE clause specifies that our actualNode should match the @id parameter we were passed, and the difference between the depth of that node, and any node we return should be at most @depth.
In order to add this operation to our provider, we just need the following:
public ArrayList GetSubChildren(int uniqueID, int depth)
{
return ProcessList("dfTreeGetSubChildren",
new SqlParameter("@id",uniqueID),
new SqlParameter("@depth",depth));
}
In our ASP.NET page, we modify the categoryList repeater so that it includes a call to a new function RepeatString that will repeat the string "--" according to the difference between the depth of the node we're displaying, and the actual node we're viewing (currentNode).
<asp:Repeater Runat="server" ID="categoryList">
<ItemTemplate>
<%# RepeatString("--",(int)DataBinder.Eval(Container.DataItem,"Depth") - currentNode.Depth - 1) %>
<a href='<%# "TreeDemoC.aspx?id=" + DataBinder.Eval(Container.DataItem,"UniqueID") %>'
><%# DataBinder.Eval(Container.DataItem,"Name") %></a>
</ItemTemplate>
<SeparatorTemplate> <br /> </SeparatorTemplate>
</asp:Repeater>
The currentNode variable is declared as
protected TreeNode currentNode;
In the code-behind class. Until now we haven't actually needed a direct reference to the "current node" - but we can do this easily enough, as our call to GetPath already returns us the node we want - at the end of the list:
ArrayList pathList = treeProvider.GetPath(treeID);
// fetch the current node
currentNode = (TreeNode)pathList[pathList.Count - 1];
// continue the binding as we did before
breadcrumb.DataSource = pathList;
breadcrumb.DataBind();
// now bind the list (we need a ref to currentNode first)
categoryList.DataSource = treeProvider.GetSubChildren(treeID,2);
categoryList.DataBind();
After adding a few more rows to the database, your page should look something like this:
This could easily be modified to display the entire tree below the current category by simply removing the depth restriction on the data we return. To do this, you can simply comment out the AND t.depth - m.depth <= @depth part of the dfTreeGetSubChildren stored procedure. Then you'd get something that looks like this:
Cool, eh?
We've only scratched the surface here of the sorts of queries you can perform over the tree now that we've got the self-maintaining lineage and depth fields. I'm not sure how much interest there will be in this article - but if you find some other useful queries you'd like to share, then just let me know.