The cfoutput tag has
an attribute called group that lets you to group
output from your record sets before displaying it to the browser.
There are two ways to use the group attribute of
the cfoutput tag. The first method uses
group to remove any duplicate rows from the query
result set.[5] This
is useful in situations where the result set you return from a query
contains duplicate rows of data but you want to display only unique
records.
Example 4-3 demonstrates what happens when you query
a table containing duplicate values and output the results without
using the group attribute of the
cfoutput tag.
Example 4-3. Failing to use the group attribute results in duplicate values in the output
<cfquery name="GetDepartment" datasource="ProgrammingCF">
SELECT Department
FROM EmployeeDirectory
ORDER BY Department
</cfquery>
<html>
<head>
<title>Failing to Use the group Attribute Results in Duplicate Values in the
Output </title>
</head>
<body>
<h2>Departments:</h2>
<cfoutput query="GetDepartment">
#Department#<br>
</cfoutput>
</body>
</html>As you can see in Figure 4-2, executing the template results in many of the same values being output more than once.
This is easy enough to fix. To remove the duplicates from the output,
all you have to do is modify the line of code containing the
cfoutput tag to read like this:
<cfoutput query="GetDepartment" group="Department" groupcasesensitive="No">
Adding
group="Department" to the
cfoutput tag tells ColdFusion to discard any
duplicate values in the result set and output only unique values. The
groupcasesensitive attribute indicates whether
grouping should be case insensitive or case sensitive. This attribute
is optional and defaults to Yes. For our example,
set groupcasesensitive to No in
case someone enters the name of a department using the wrong case.
The difference in output is shown in Figure 4-3.
It is important to note that using
group to remove duplicates from the result set
does so after the result set is returned from
the database. You should consider how this might affect the
performance of your application if you want to return only a few
records from a large record set that contains numerous duplicate
values. In such a case, you should use SQL to remove the duplicates.
If you look at the code in Example 4-3,
you’ll notice that we included ORDER
BY
Department in our SQL statement. It
is necessary to sort the result set by the column being grouped. To
see what happens if you don’t include the
ORDER BY clause, remove it from the query and
execute the template.
As I mentioned in the beginning of this section,
group can be used in two ways. The second way the
group attribute can be used is to group like
records for output. This allows you to do such things as group the
output of a query by a certain field or fields such as age, gender,
department, color, etc. This is done by including (nesting) a second
set of cfoutput tags without the
group attribute inside the first set. Example 4-4 shows how to use the group
attribute of the cfoutput tag to group the results
of a query by Department.
Example 4-4. Using the cfoutput tag’s group attribute to group records by department
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
SELECT Name, Title, Department, Email, PhoneExt
FROM EmployeeDirectory
ORDER BY Department
</cfquery>
<html>
<head>
<title>Using the group Attribute of the cfoutput Tag to Group Records by
Department</title>
<style type="text/css">
th {
background-color : #888888;
font-weight : bold;
text-align : center;
}
td {
background-color : #C0C0C0;
}
td.Group {
background-color : #FFFFFF;
font-size : 14pt;
font-weight : bold;
text-align : center;
}
</style>
</head>
<body>
<table cellpadding="3" cellspacing="1">
<cfoutput query="GetEmployeeInfo" group="Department" groupcasesensitive="No">
<tr>
<td class="Group" colspan="5">#Department#</td>
</tr>
<tr>
<th>Name</th>
<th>Title</th>
<th>Department</th>
<th>E-mail</th>
<th>Phone Extension</th>
</tr>
<cfoutput>
<tr>
<td>#Name#</td>
<td>#Title#</td>
<td>#Department#</td>
<td><a href="Mailto:#Email#">#Email#</a></td>
<td>#PhoneExt#</td>
</tr>
</cfoutput>
<tr>
<td class="Group" colspan="5"> </td>
</tr>
</cfoutput>
</table>The group attribute of the
cfquery tag lets you group your
query’s result set by the specified column. In this
case, we want to group the query results by Department. It is
necessary to use the ORDER BY clause to order the
result set by the column being grouped. Failing to do so results in
unwanted output. Notice the second set of cfoutput
tags nested within the pair declaring the group.
This creates an outer and inner loop for looping over the result set
and grouping the output appropriately. Nested
cfoutput tags may be used only when the outermost
cfoutput tag has a value specified for the
query and group attributes. If
you attempt to nest cfoutput tags without using
these attributes in the outermost tag, ColdFusion throws an error.
Additionally, if you omit the nested cfoutput, the
nested grouping doesn’t occur, and you end up
removing any duplicate records from the result set (just like our
previous example). Executing the template results in the output shown
in Figure 4-4.
It is entirely
possible to group data several levels deep. Doing so requires nesting
several sets of cfoutput tags using the following
general syntax:
<cfoutput query="query_name" group="column">
HTML and CFML...
<cfoutput group="different_column">
HTML and CFML...
<cfoutput>
HTML and CFML...
</cfoutput>
</cfoutput>
</cfoutput>When ColdFusion encounters nested cfoutput tags,
it executes successive levels of nested loops to group the query
result set. In general, there are a few rules you need to keep in
mind when working with nested cfoutput tags:
[5] Don’t confuse the group attribute of the cfoutput tag with the SQL group BY keyword, because they perform entirely different functions. The SQL group BY keyword is discussed in Chapter 11.
Get Programming ColdFusion MX, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
