HomeArticlesTutorialsExtensionsSupportThe Team

Nested Repeat Regions Using ColdFusion

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.

Tom Muck

www.dwteam.com

Copyright © 2002, DWTeam.com. All Rights Reserved.