One of the frequent questions in the newsgroups is "How do I nest a repeat region?" Unfortunately, many of these people are using such technologies as ASP, PHP, or JSP. Using any of those technologies it is not easy or intuitive to create a nested region. ColdFusion users, on the other hand, have an attribute in the <cfquery> tag that facilitates easy nested regions: GROUP.

If you are unaware of what a nested region is, consider the following:

Let?s say you have a list of movie categories and titles, and you want the titles to populate under the movie categories. Typically this involves writing a query that will join two tables — a Categories table and a Movies table. The data might look like this:

• Categories
• CategoryID
• CategoryDescription

• Movies
• MovieID
• MovieTitle
• CategoryID

The tables obviously have one field in common -- the CategoryID field in the Movies table is a foreign key to the Categories table. If you don?t know what a foreign key is, it may be time to visit the library and pick up a good book on database design, such as Database Design For Mere Mortals, by by Michael J. Hernandez (ISBN: 0201694719).

The SQL statement to retrieve the information that you need from the database will look like this:

<cfquery name="rsGetMovies" datasource=#mydsn#>
SELECT m.MovieID, m.MovieTitle, c.CategoryDescription
FROM Movies m
INNER JOIN
Categories c
ON m.CategoryID = c.CategoryID
</cfquery>

That will return your data in a format like this:

1,Monty Python and the Holy Grail,Comedy

2,Dracula,Horror

3,Frankenstein,Horror

4,Enter the Dragon,Action

5,The Crow,Action

6,Blazing Saddles,Comedy

7,Animal House,Comedy

8,The Matrix,Action

9,Godzilla: King of the Monsters,Horror

10,The One-Armed Boxer,Action

To turn this into a query that can be used by ColdFusion to create a nested resultset, you have to simply add an order by clause on the CategoryDescription field. That will allow you to group the categories.

<cfquery name="rsGetMovies" datasource=#mydsn#>
SELECT m.MovieID, m.MovieTitle, c.CategoryDescription
FROM Movies m
INNER JOIN
Categories c
ON m.CategoryID = c.CategoryID
ORDER BY CategoryDescription
</cfquery>

Now the resultset is ordered like this:

4,Enter the Dragon,Action

5,The Crow,Action

8,The Matrix,Action

10,The One-Armed Boxer,Action

1,Monty Python and the Holy Grail,Comedy

6,Blazing Saddles,Comedy

7,Animal House,Comedy

2,Dracula,Horror

3,Frankenstein,Horror

9,Godzilla: King of the Monsters,Horror

To return the results to the page in a nested region, you can use a table to format the results, just as you would normally do if you weren?t nesting the results. The only difference is that you will use a GROUP attribute in your <cfoutput> statement, and you will then use a second set of <cfoutput> tags within the first set. This is normally not legal in your ColdFusion pages, except when you use a GROUP attribute in a <cfoutput>. Note that we are grouping on Categories:

<table>
  <cfoutput query="rsGetMovies" group="CategoryDescription">
    <tr>
      <td>#rsGetMovies.CategoryDescription#</td>
      <td>&nbsp;</td>
    </tr>
    <cfoutput>
      <tr>
        <td>&nbsp;</td>
        <td>#rsGetMovies.MovieTitle#</td>
      </tr>
    </cfoutput>
  </cfoutput>
</table>

Using that formula, the output will look something like this:

The grouped <cfoutput> statements cause the CategoryDescription field to be used as a nesting group.

The same thing can be used inside of a <cfmail> tag when sending an email. This can be used when sending out past-due notices, or order notices. The <cfmail> tag is a lot like a <cfoutput> tag -- you can specify a QUERY attribute and a GROUP attribute. The <cfquery> for an email might look like this:

<cfquery name="rsGetEmails" datasource=#mydsn#>
SELECT c.email, c.name, o.itemno, o.itemname
FROM Customers c
INNER JOIN
Orders o
ON
c.CustID = o.CustID
ORDER BY c.email
</cfquery>

The <cfmail> tag that makes use of the nested region inside of the body of the email would look like this:

<cfmail to="rsGetEmails.email" from="admin@mycoolestore.com"
 query="rsGetEmails" group="email">
Hello #rsGetEmails.name#:

Your order consists of the following items:

<cfoutput>
#rsGetEmails.itemno# #chr(10)#
#rsGetEmails.itemname# #chr(10)#
</cfoutput>

Thank you for your order!
</cfmail>

When the customer receives the email, all of the products that are listed in the Orders table under that customer?s CustID number will be listed in the email, because they are nested inside of <cfoutput> tags within the loop. Also, because the <cfmail> tag is like a <cfoutput>, the emails will go out one per customer to all customers in the Customers table.

You can see how easy and intuitive the nested region is in ColdFusion. This can be expanded to several levels, as well. For example, a query I recently wrote at Community MX to display all articles along with categories and levels. The articles were to be grouped by two different levels: reader level and article category. The tables look like this:

• Articles
  • Title
  • ID
  • ReaderLevel
  • Category
• Categories
  • CategoryID
  • Category
• ReaderLevels
  • ReaderLevelID
  • ReaderLevel

The query looked like this:

SELECT a.ID, a.Title, ca.Category, L.ReaderLevel
FROM Articles a
INNER JOIN Categories ca ON a.Category = ca.CategoryID
INNER JOIN ReaderLevel L ON L.ReaderLevelID = a.ReaderLevel
ORDER BY ca.Category ASC, L.ReaderLevel ASC

The code looked something like this:

<ul>
 <cfoutput query="rsContent" group="Category">
  <li><strong>#rsContent.Category</strong>#
    <ul>
      <cfoutput group="ReaderLevel">
        <li>#rsContent.ReaderLevel#
        <ul><cfoutput>
          <li>#rsContent.Title# </li>
          </cfoutput>
       </ul>
      </li>
     </cfoutput>
    </ul></li>
  </cfoutput>
</ul>

We start with an unordered list (<ul>). The outer <cfoutput> repeats the category description (Category) as a list item (<li>). The list item contains another unordered list. Inside that <ul> tag is another <cfoutput>, which groups ReaderLevel. ReaderLevel is displayed in a list item, with another unordered list inside. Inside that unordered list is the third <cfoutput>, which loops over the titles within each ReaderLevel. The end effect is something like this (clipped for brevity):

• Dreamweaver
  • All Readers
    • ASP.NET and Dreamweaver's Application Panel
    • DW MX 2004 Updater - Expect Better Performance!
    • Creating a Simple Horizontal Looper in ASP.NET
    • etc
  • Beginner
    • Defining a Site in DreamweaverMX - The Advanced Tab
    • Defining a Site in DreamweaverMX - The Basic Tab
    • Disjointed Rollovers
    • etc
  • Beginner - Intermediate
    • Creating a Simple Server Behavior - Part II
    • The Long and Short of Dreamweaver Timelines - An Introduction
    • Creating a Guestbook - Part II
    • etc
  • Intermediate
    • Regular Expressions: An Introduction
    • JavaScript Code Hints in Dreamweaver
    • etc
  • Intermediate - Advanced
    • Sending and Receiving Data within Dreamweaver Extensions - Part IV
    • Sending and Receiving Data within Dreamweaver Extensions - Part I
    • etc
• Flash
  • All Readers
    • FStyleFormat Demystified -- Part 1
    • Flash Your Cookies
    • Suffix Abbrevations to make your code easier to write
    • etc
  • Beginner
    • The Basics of Flash Buttons and Navigation Bars
    • Creating a Silhouette Effect in Flash
    • etc
  • Beginner - Intermediate
    • Overcoming Your Fear of Flash--Part II
    • Creating Shadow Effects in Flash
    • etc

You can see that you can nest several levels and make quite complex structures based on one simple query. This can go many more levels than what is shown here as well. The technique can also be used to nest levels of JavaScript menus, tables, and other types of dynamic displays. But that is for another article.