Grouping Result Output

Before a new level of complexity is introduced, let's review how ColdFusion processes queries.

In ColdFusion, data queries are created using the <cfquery> tag. <cfquery> performs a SQL operation and retrieves results if any exist. Results are stored temporarily by ColdFusion and remain only for the duration of the processing of the template that contained the query.

The <cfoutput> tag is used to output query results. <cfoutput> takes a query name as an attribute and then loops through all the rows that were retrieved by the query. The code block between <cfoutput> and </cfoutput> is repeated once for each and every row retrieved.

All the examples created until now displayed results in a single list or single table.

What would you do if you wanted to process the results in subsets? For example, suppose you wanted to list movies by rating. You could change the SQL statement in the <cfquery> to retrieve the rating ID and set the sort order to be RatingID and then by MovieTitle.

This would retrieve the data in the correct order, but how would you display it? If you used <cfoutput> as you have until now, every row created by the <cfoutput> block would have to be the same. If one had the rating displayed, all would have to because every row that is processed is processed with the same block of code.

Look at Figure 10.11. As you can see, the screen contains nested lists. The top-level list contains the rating IDs, and within each rating ID is a second list containing all the movies with that rating. How would you create an output like this?

Figure 10.11. Grouping lets you display data grouped into logical sets.

Listing 10.9 contains the code for a new page; save this as ratings1.cfm and execute it in your browser.

Listing 10.9. `ratings1.cfm`Grouping Query Output

 <!--- Name:        ratings1.cfm Author:      Ben Forta (ben@forta.com) Description: Query output grouping Created:     12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, RatingID FROM Films  ORDER BY RatingID, MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head>  <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> <!--- Display movie list ---> <ul>  <!--- Loop through ratings --->  <cfoutput query="movies" group="RatingID">   <li>#RatingID#</li>   <ul>    <!--- For each rating, list movies --->    <cfoutput>     <li>#MovieTitle#</li>    </cfoutput>   </ul>  </cfoutput> </ul> </body> </html>

Listing 10.9 starts with the comment block, followed by a <cfquery> that retrieves all the movies (title and rating only) sorted by RatingID and MovieTitle (by RatingID and within each RatingID by MovieTitle).

The display section of the code starts by creating an unordered listthis is the outer list, which contains the ratings.

Then, <cfoutput> is used again to create an output block, but this time the group attribute has been added. group="RatingID" tells the output block to loop through the outer loop only when RatingID changes. In other words, the outer loop is processed once per group value. So, in this example, it's processed once per RatingID valueregardless of the number of movies with that RatingID.

Then the RatingID is displayed, and a second unordered list is startedthis is for the inner list within each RatingID.

Next, comes a second <cfoutput> block that displays the MovieTitle. No query is specified here; ColdFusion doesn't need one. Why? Because group is being used, ColdFusion knows which query is being used and loops through the inner <cfoutput> only as long as RatingID doesn't change.

As soon as RatingID changes, the inner <cfoutput> loop stops and the inner list is terminated with a </ul>.

This repeats until all rows have been processed, at which time the outer <cfoutput> terminates and the final </ul> is generated.

So, how many times is each <cfoutput> processed? The movie list contains 23 rows with a total of 6 ratings. So the outer loop is processed 6 times, and the inner loop is processed 23 times. This outer list contains 6 items (each RatingID value), and each item contains a sub-list containing the movies with that RatingID.

NOTE

For grouping to work, groups must be created in the exact same order as the sort order (the ORDER BY clause) in the SQL statement itself.

Listing 10.10 contains a modified version of Listing 10.9, this time displaying the results in an HTML table (as seen in Figure 10.12). Save Listing 10.10 as ratings2.cfm, and then execute it in your browser.

Listing 10.10. `ratings2.cfm`Grouping Query Output

 <!--- Name:        ratings2.cfm Author:      Ben Forta (ben@forta.com) Description: Query output grouping Created:     12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, RatingID FROM Films  ORDER BY RatingID, MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head>  <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> <!--- Display movie list ---> <table>  <!--- Loop through ratings --->  <cfoutput query="movies" group="RatingID">   <tr valign="top">    <td>Rating #RatingID#</td>    <td>     <!--- For each rating, list movies --->     <cfoutput>      #MovieTitle#<br>     </cfoutput>    </td>   </tr>  </cfoutput> </table> </body> </html>

Figure 10.12. Grouped data can be used in lists, tables, and any other form of data presentation.

The only thing that has changed in Listing 10.10 is the output code. Again, the <cfoutput> tags are nestedthe outer loops through RatingID and the inner loops through the movies.

The HTML table is created before any looping occurs (you want only one table). Then, for each RatingID a new table row is created containing two cells. The left cell contains the RatingID, and the right cell contains the movies.

To do this, the inner <cfoutput> loop is used in that right cell (between the <TD> and </td> tags) so that, for each RatingID listed on the left, all the appropriate movies are listed on the right.

TIP

A single level of grouping is used here, but there is no limit to the number of levels in which data can be grouped. To group multiple levels (groups within groups), you simply need an additional <cfoutput> per group (and of course, the SQL statement must sort the data appropriately).

Figure 10.11. Grouping lets you display data grouped into logical sets.

Listing 10.9. ratings1.cfmGrouping Query Output

Listing 10.10. ratings2.cfmGrouping Query Output

Figure 10.12. Grouped data can be used in lists, tables, and any other form of data presentation.

Listing 10.9. `ratings1.cfm`Grouping Query Output

Listing 10.10. `ratings2.cfm`Grouping Query Output