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

One of the Stupid-Web-Programming-Tricks I end up recoding quite often is a sorter for queries with Parent-Child relationships. Trees, basically. You know the situation: some yahoo wants theoretically infinite levels of depth to his navigational menus or his product categories for his e-commerce system. What you end up with is a table in a database that looks like this:

ItemID Title ParentID
12 Coding 0
26 Web 12
15 Applications 12
9 Hybrid 12
7 Food 0
8 C++ 15
19 Perl 9
16 ColdFusion 26
1 Cheese 7

This should magically yield a list that looks like this:

  1. Coding
    1. Applications
      1. C++
    2. Hybrid
      1. Perl
    3. Web
      1. ColdFusion
  2. Food
    1. Cheese

Getting from Point A to Point B isn’t all that hard, especially when you have the time, foresight, and ability to plan ahead. There are dozens of different ways to accomplish this efficiently within the database. Most of the time, however, I find that I have read-only access to the database table and end up having to do the organization in code. In this series of posts I’ll be explaining my solution and how I get to it.