http://www.cnn.com/2007/TECH/ptech/08/08/mac-collector.ap/

Ok so its not Bill Gates, but a guy by the name of  Jeremy Mehrle likes to collect Apple computers.  As you will see in the video he has a pretty nice collection…I saw at least two models I have sitting in my garage.

In a recent post I wrote about a flexible structure used for storing and retrieving classifications form a database. My example retrieved “entities” for a single classification, and those entities that matched any of that classifications children.

In this entry I want to expand on this approach, and demonstrate how results can be limited to only those entities that match a specific list of classifications.

Using the tables above, let retrieve all entities that are related to ClassificationId 9 (Times New Roman – Bold) AND ClassificationId 4 (Blue).

At first glance I thought this was pretty simple and used the IN clause to match my list of classification ids.

1
2
3
4

SELECT * FROM Entity 
LEFT JOIN EntryClassification 
ON Entity.Id = EntryClassification.EntityId 
WHERE EntryClassification.ClassificationId in (9,4)

Unfortunately this approach fails to return the correct result set.  Instead of retrieving those entities that are related to BOTH classifications, the IN clause determines whether a specified value matches ANY value in the list. 

After quite a few experimentations , here is the solution I came up with.

1
2
3
4
5

SELECT * FROM Entity WHERE Entity.Id in 
(SELECT ec.EntityId FROM EntityClassification ec
WHERE ec.ClassificationId in (9,4) 
GROUP BY ec.EntryId 
HAVING Count(ec.EntryId) = 2))

If the number of items in your list will change, then you will need to either pass in the number of items or calculate the total by examining the string of Ids.  If you always know the delimiter, the following is an interesting hack to determine the number of items.

1
2
3

DECLARE @ClassificationIds varchar(10) = '9,4'
DECLARE @Count int
SET @Count = (LEN(@ClassificationIds ) - LEN(REPLACE(@ClassificationIds , ',', ''))+1)

Saturday, September 15th, 2007
Desert Code Camp is a free, one-day event put on by the local Phoenix community to help promote software development. Code Camp will be held at the University of Advancing Technology (UAT) in Tempe. Check out the site to see a full list of the topics that will be presented. As part of the C# track, I will present an introduction to Generics, Predication and Anonymous delegates. The track title is “C# 2.0 Language Features for Late Comers“.

University of Advancing Technology
2625 W. Baseline Road (map)
Tempe, Arizona 85283

Today one of my coworkers pointed me to an interesting article on what it means to be a “Front-End Architect“.  I really enjoyed this article and encourage everyone to take a look.  From a background in Software Engineering I am not totally convinced that “Architect” is the appropriate noun, in my experience, “Architect” is reserved for those that design, not implement.  Nonetheless, Front-End skills are essential to implementing evolving front-end technology. I agree with the author that every interactive company needs someone that “truly understands and is experienced with everything that touches the front-end”.    Here is a list of skills the author sees as essential:

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.

From this table we can visualize a simple nested tree of classifications

• Attributes
  • Color
    • Red
    • Blue
    • Green
    • Purple
  • Font
    • Times New Roman
      • Bold
      • Italic
    • Arial
      • Bold
      • Calibri

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.

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))