Frequently our team is tasked with creating a way to categorize data. One very common and flexible approach is to create a table which contains a series of parent-child relationships. This hierarchical approach can be extremely flexible and efficient for categorizing multiple levels of content. Here is an example of a table used to store this type of classification.
| Id |
ParentId |
Name |
| 1 |
1 |
Attributes |
| 2 |
2 |
Color |
| 3 |
2 |
Red |
| 4 |
2 |
Blue |
| 5 |
2 |
Green |
| 6 |
2 |
Purple |
| 7 |
1 |
Font |
| 8 |
7 |
Times New Roman |
| 9 |
8 |
Bold |
| 10 |
8 |
Italic |
| 11 |
7 |
Arial |
| 12 |
11 |
Bold |
| 13 |
7 |
Calibri |
| 14 |
13 |
Bold |
From this table we can visualize a simple nested tree of classifications
From a SQL perspective, interacting with this structure is quite painless. Through the use of a composite table, this structure lends itself well to having entities with multiple classification.
| ClassificationId |
EntityId |
| 4 |
1 |
| 9 |
1 |
| 3 |
2 |
| 10 |
2 |
If a user wants entities which are classified as “Times New Roman” “Bold” they restrict the results to those with a ClassificationId = 9.
Now what if they want are those entities which have any of the “Font” sub classifications (ClassificationId = 7,8,9,10,11,12,13,14)?
This is where it gets a bit more tricky. The approach I have found most flexible is to create a custom table-valued function which recursively walks the table and returns all children of the specified parent. The results of this reqursive query are returned in the form of a temporary named result set, known as a common table expression (CTE). If our desire was to return more than just the classifications Id we could use this CTE as a sub query on an additional query.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| ALTER FUNCTION [Resource].[ClassificationChildren]
(
@ClassificationId int
)
RETURNS TABLE
AS
RETURN
( WITH Classes (Id, ParentId, [Level]) AS
(
-- Create the anchor query. This establishes the starting point
SELECT c.[Id],c.[ParentId],0
FROM Classification c
WHERE c.[Id] = @CategoryId
UNION ALL
-- Create the recursive query. This query will be executed
-- until it returns no more rows
SELECT
c.[Id],c.[ParentId], b.[Level]+1
FROM Classification c
INNER JOIN Classes b ON COALESCE(c.[ParentId],0) = b.[Id]
)
SELECT k.Id FROM Classes k
) |
Once I have this function, I can create a query to give me the results I desire.
1
2
3
4
| SELECT * FROM Entity LEFT JOIN
EntryClassification ON Entity.Id = EntryClassification.EntityId
WHERE EntryClassification.ClassificationId in
(SELECT Id FROM ClassificationChildren(7)) |