Grouping Output

At times, the ability to group ColdFusion output data can make your presentation much cleaner and easier for your visitors to understand. 

Consider for example, a Web site that displays information about authors and books would probably want to display their information in a format like:

Author Name:

• book name
• book name

Author Name:

• book name
• book name

At the very least, they would likely have a database with the following two tables: tblAuthor and tblBook, which would look like:

Notice the relationship between the two tables. authorID is the primary key in the tblAuthor table. It is a unique identifier, and each value can only appear once. authorID also appears in the tblBook table. In this case, it is a foreign key and each value can appear many times. This is known as a one-to-many relationship between tables. Each one author can have many books.

Now, let?s say we want to query the tables to grab the author and book title information.

<cfquery name=?getAuthorsAndBooks? datasource=?myDSN?>
        SELECT      tblAuthor.authorID, 
                        tblAuthor.authorName,
                        tblBook.bookID,
                        tblBook.bookTitle
        FROM         tblAuthor INNER JOIN tblBook ON tblAuthor.authorID = tblBook.authorID
        ORDER BY   tblAuthor.authorName, tblBook.bookTitle
</cfquery>

The results might not be what you expect. 

As you can see in the <cfdump> above, each author?s name is output multiple times (once for each book they?ve written). Recalling our desired output (from the beginning of the tutorial), we might try to do the following:

<cfoutput query=?getAuthorsAndBooks?>
    <b>#authorName#</b>
    <ul>
        <li>#bookTitle#</li>
    </ul>
</cfoutput>

But because our query returns each author multiple times (once for each book he or she has written), we get the following output:

Ben Forta 

• ColdFusion MX Web Application Construction Kit 

Ben Forta 

• Teach Yourself SQL in 10 Minutes

J. K. Rowling 

• Harry Potter and the Chamber of Secrets 

J. K. Rowling 

• Harry Potter and the Prisoner of Azkaban 

J. K. Rowling 

• Harry Potter and the Sorcerer's Stone 

Steven King 

• Carrie

Steven King 

• Cujo 

Steven King 

• The Shining

William Shakespeare 

• Julius Caesar

William Shakespeare 

• King Lear 

William Shakespeare 

• Romeo and Juliet

William Shakespeare

• The Taming of the Shrew 

This is obviously, not what we want. We?re looking to get the following output:

Ben Forta

• ColdFusion MX Web Application Construction Kit
• Teach Yourself SQL in 10 Minutes

J.K. Rowling

• Harry Potter and the Chamber of Secrets
• Harry Potter and the Prisoner of Azkaban
• Harry Potter and the Sorcerer?s Stone

Steven King

• Carrie
• Cujo
• The Shining

William Shakespeare

• Julius Caesar
• King Lear
• Romeo and Juliet
• The Taming of the Shrew

We can probably do it in two queries...one to query the author table and another to query the book table...but that?s twice as many trips to the database as we need. We can do it with our original query, using the data displayed in the <cfdump> above.

We?re going to use the GROUP attribute of the <cfoutput> tag. In our query above, we did:
<cfoutput query=?getAuthorsAndBooks?>


Now let?s change it just a bit. It?s going to change to:
<cfoutput query=?getAuthorsAndBooks? group=?authorname?>

As this would imply, it directs ColdFusion to display the output in groups, rather than once for each record. In this case, the column to group on is authorname. 

Think of a group as if you had each of these books in front of you. If you put each book into a separate pile (based on author), you would have effectively grouped each book. You would have a pile for Ben Forta, a pile for Shakespeare, etc. Each pile represents a group. While each pile (group) has many books in it, there is only one pile per author.

So if <cfoutput query=?getAuthorsAndBooks? group=?authorname?> defines the ?piles?...how do the piles get populated? Interestingly enough, with a nested <cfoutput> tag (the only time <cfoutput> tags can be nested is when a GROUP attribute is used on the outer tag). 

Here?s the whole output:

1. <cfoutput query="getAuthorsAndBooks" group="authorname">
2. <b>#authorName#</b>
3. <ul>
4. <cfoutput>
5. <li>#bookTitle#</li>
6. </cfoutput>
7. </ul>
8. </cfoutput>

...and a line-by-line explanation:

This produces the desired output. Each author name is displayed only once, with the list of books they?ve written displayed under their name.

You should now have a full understanding of how to group your output. You can stop reading now...or dare to continue, where I?m going to try and thoroughly confuse you :)

You see...can also group multiple times. If the book database we used in this tutorial were a database for an actual library or bookstore, there would be much more detail involved. Genre would likely be part of the database. If that were the case, you could group on genre, then author, and then list the books. 

To continue with the piles analogy...let?s say there were circles on the ground. Each circle represented a genre. Within the ?suspense? circle, would be a pile for Steven King, a pile for Clive Barker, etc. Within the ?classics? circle, there would be piles for Shakespeare, Chaucer, etc. It?s just a larger group containing a smaller group that ultimately contains individual items.

I?ve added one additional author to the authors table, and two additional books to the books table...along with an additional column in the books table (genre). The one-to-many relationship still exists. So our tables now look like:

Adding the new column to our query, we now have:

<cfquery name="getAuthorsAndBooks" datasource="group">
    SELECT                tblAuthor.authorID, 
                                     tblAuthor.authorName,
                              tblBook.bookID,
                              tblBook.bookTitle,
                              tblBook.genre
    FROM                   tblAuthor INNER JOIN tblBook ON tblAuthor.authorID = tblBook.authorID
    ORDER BY             tblBook.genre
                              tblAuthor.authorName,
                              tblBook.bookTitle
</cfquery>

Notice we also added the new column to the ORDER BY clause of our SQL statement. When grouping output, the column on which you?re grouping must be included in the ORDER BY. Not following this rule will not generate an error...but will almost certainly produce an unexpected output.

Our new query produces the following recordset:

As you can see, the only difference is the additional column for genre, as well as the 2 extra records returned (authorname J.R.R. Tolkien).

This is our desired output:

Classics

• William Shakespeare
  • Julius Caesar 
  • King Lear 
  • Romeo and Juliet 
  • The Taming of the Shrew 

Fantasy

• J. K. Rowling 
  • Harry Potter and the Chamber of Secrets 
  • Harry Potter and the Prisoner of Azkaban 
  • Harry Potter and the Sorcerer's Stone 
• J.R.R. Tolkien 
  • Lord of the Rings 
  • The Hobbit 

Reference 

• Ben Forta 
  • ColdFusion MX Web Application Construction Kit 
  • Teach Yourself SQL in 10 Minutes 

Suspense 

• Steven King 
  • Carrie 
  • Cujo
  • The Shining

You can see that the outermost group is genre (the circles on the floor). Our ?piles? are intact, as each ?circle? contains a pile defined by author (see the Fantasy group, which contains J.K. Rowling and J.R.R. Tolkien). And then of course, each individual book in each of the piles.
The code to achieve this is:

<cfoutput query=?getAuthorsAndBooks? group=?genre?>
    <u>#genre#</u>
    <ul>
    <cfoutput group=?authorName?>
        <li><b>#authorName#</b></li>
        <ul>
        <cfoutput>
            <li>#bookTitle#</li>
        </cfoutput>
    </ul>
    </cfoutput>
    </ul>
</cfoutput>

I?m going to forego the line-by-line explanation, as it?s very similar to the code sample showing the output grouped by author. It should be self-explanatory.

And that?s where I?m going to bid you adieu (adieu). You should now be quite comfortable with grouping your <cfquery> output. 

If the code sample for the nested groups doesn?t make sense, or if you have any other questions or comments, shoot me an e-mail...or post a message in the forums. I look forward to hearing from you.