In the effort to create semantically robust websites, I am often dealing with hierarchical data from Content Management Systems. Sometimes these systems have methods for dealing with this categorization. Often these methods are either inefficient (making unnecessary calls to the database, or just extremely clumsy) Sometimes, the presentation level is custom. In .net there is the TreeView Control which has its own set of issues. Many times I get into the world of XLM and XSLT, which almost always makes programmers groan.
This essay will present a method for dealing with this and the various issues when presenting The Adjacency List Model data using T-SQL and Classic ASP.
The Adjacency List Model
A good online definition of The Adjacency List Model can be seen here: http://www.pure-performance.com/2009/03/managing-hierarchical-data-in-sql/ . In particular, we are looking at Trees and Hierarchies in SQL for Smarties
by Joe Celko.
Imagine your data looks like below. This has Devices Categories and DeviceCategories within the Device Categories. In theory, this hierarchy could go on indefinitely with categories being nested inside categories.
In the end, the goal is to represent the data above as a series of nested lists as seen below.
- iPhone 5
- iPhone 4/4S
- iPhone 3/3GS
- iPad (Retina Display)
- iPad 2
- iPad Mini
- iPod Touch
- iPod Nano
- iPod Classic
- iPod Shuffle
- Other Manufacturers
Getting From Point A to Point B
The first thing is to form the SQL data correctly. You can do this with a temp table, but I use a CTE (Common Table Expression). Below, this is what is the stored procedure sp_DisplayDeviceCategories
WITH TempCategories AS ( SELECT DeviceCategoryId, ParentCatId, DeviceCategoryName, 1 AS DeviceLevel, Cast(DeviceCategoryId as varchar(30)) AS SortOrder FROM DeviceCategories WHERE ParentCatId IS NULL UNION ALL SELECT c.DeviceCategoryId, c.ParentCatId, c.DeviceCategoryName, c2.DeviceLevel + 1, Cast(c2.SortOrder + '/' + CAST(c.DeviceCategoryId AS varchar) as varchar(30)) FROM DeviceCategories AS c INNER JOIN TempCategories AS c2 ON c.ParentCatId = c2.DeviceCategoryId WHERE c.ParentCatId IS NOT NULL ) SELECT * FROM TempCategories ORDER BY SortOrder
This then will format the data as
The tricky part of the CTE above was creating the DeviceLevel and SortOrder fields so that we can order the data is such a way that then we can simply loop through the data and display the results in a user-friendly way.
Now the Hacky Part
Well this may not actually be a hacky part. I find the .net TreeView to be pretty hacky as it uses tables to display this list data. There are CSS workarounds with the cssadapters (http://www.asp.net/cssadapters/TreeView.aspx) but yuck…. Why not get it right in the first pass.
I have also often seen people then format this sort of hierarchy with non-breaking spaces ( ) and do clever HTML things to indent lists but this is bad form. In order for your navigation to work with responsive designs it needs to be semantically correct.
I recently worked on a project that still uses Classic ASP. The problem was, without .net Controls, how to display the data above as a list. It would have to be recursive to work. In the end though, it is just procedural programming. I am certain that this has been created in other languages and for various custom navigation components.
<% Sub DisplayDevices ' this sub writes out a nested HTML list from a common table expression in an SQL Stored Procedure ' that uses Adjacency list model to display Devices Categories DeviceLevelTemp = 0 WriteListItem = True sql = "EXEC sp_DisplayDeviceCategories" set rs=Server.CreateObject("ADODB.Recordset") rs.open sql,connect If Not rs.eof Then DO UNTIL rs.eof DeviceCategoryId = rs("DeviceCategoryId") ParentCatId = rs("ParentCatId") DeviceCategoryName = rs("DeviceCategoryName") DeviceLevel = rs("DeviceLevel") If DeviceLevelTemp = DeviceLevel Then response.write("") WriteListItem = True Else If DeviceLevel > DeviceLevelTemp Then c = DeviceLevel - DevicelevelTemp For i = 1 To Cint(c) response.write("
- ") Next WriteListItem = False End If 'closure If DeviceLevelTemp > DeviceLevel Then c = DeviceLevelTemp - Devicelevel response.write(" ") For i = 1 To Cint(c) response.write("
" & DeviceCategoryName & "") Case 2 response.write("
" & DeviceCategoryName & "") Case Else response.write("" & DeviceCategoryName & "") End Select DeviceCategoryIdTemp = DeviceCategoryId ParentCatIdTemp = ParentCatId DeviceCategoryNameTemp = DeviceCategoryName DeviceLevelTemp = DeviceLevel rs.movenext LOOP 'clean up list For i = 1 To Cint(DeviceLevel) response.write("
For starters, EXEC sp_DisplayDeviceCategories is simply the CTE query above. The basic concept is that the results are then put into nested lists. When the records are all listed, the subroutine then cleans up the HTML and closes the list. I have tested this with only one set of data. Let me know if the concepts are of use to you. It would be nice to make the Sub DisplayDevices a more universal routine, but the data may always be different depending on the field names.