Writing a Parent-Child Query Sorter in CF, Part 2

The first thing we need to worry about is how to get our data out of the database. A novice would probably start off with something like this:

SELECT ItemID, Name, ParentID
FROM Stuff

This makes the assumption that all of the items were entered in the order that they should come back out. This is very rarely the case. Similarly, this won’t work:

SELECT ItemID, Name, ParentID
FROM Stuff
ORDER BY ParentID, Name

All that gains us is that we now know that we’re almost in alphabetical order. Somewhat. But really, our same problem from last time comes up: we just can’t trust the IDs to be in any sort of order. We really need to be ordering by the Name.

A more SQL-savvy coder might then try this:

SELECT s1.ItemID, s1.Name, s1.ParentID
FROM Stuff AS s1 LEFT OUTER JOIN STUFF AS s2 ON (s1.ParentID = s2.ItemID)
ORDER BY s2.Name, s1.Name

A join has been added, so that we’re now ordering by the Parent’s name. This will work just fine … if we’re limited to only one level of parent-child relationships. Given our test data, we know that we’re not. Trying to out-guess the user and add more joins is only going to get us into more trouble (and slow down the query).

Trying to do parent-child relationships in straight SQL is next to impossible given the open-ended scenario we’ve got here. There are solutions if your DB engine supports parent-child queries natively, as well as solutions that use stored procedures. But what if you don’t have access to either of those?

For now, let’s just get the data out in the order we’d like to see it if it had no parent-child relationships:

SELECT ItemID, Name, ParentID
FROM Stuff

Next time we’ll look at how we can re-sort the query with ColdFusion.

By Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.