ColdFusion 10 - Using The Group Attribute With CFLoop To Group Query Rows
Posted April 9, 2012 at 10:21 AM by Ben Nadel
Before ColdFusion 10, the "group" attribute was only available on the CFOutput tag. This attribute could be used to create pseudo "GROUP BY" behavior when outputting ColdFusion query data. With ColdFusion 10, the "group" attribute can now be used with the CFLoop tag when looping over a query object. In most cases, I prefer executing ColdFusion's query-of-queries to produce this behavior; but, in a small set of use cases (like creating drop-down menus), I can definitely see the "group" attribute making life a lot easier.
NOTE: At the time of this writing, ColdFusion 10 was in public beta.
The "group" attribute works by grouping together adjacent rows with matching column values - the target column is determined by the "group" attribute. Once grouped, you can then loop over the grouped columns within a subsequent CFLoop tag. Unlike the GROUP BY clause in a SQL query, the "group" attribute depends on query that has been pre-sorted on the target column.
To demonstrate this feature, I've constructed a query with Male and Female friends. The query is already sorted such that all Female friends are listed first. I will then output the query in two distinct gender groups using the "group" attribute:
- <!---
- Create a query with sibling rows that have matching data. We'll
- use the GROUP attribute to condence similar rows to a single
- outer row of our query.
-
- NOTE: We are using the new QueryNew() features of ColdFusion 10
- to populate the new query.
- --->
- <cfset friends = queryNew(
- "id, name, gender",
- "cf_sql_integer, cf_sql_varchar, cf_sql_varchar",
- [
- [ 1, "Sarah", "Female" ],
- [ 2, "Tricia", "Female" ],
- [ 3, "Joanna", "Female" ],
- [ 4, "Arnold", "Male" ],
- [ 5, "Vin", "Male" ]
- ]
- ) />
-
-
- <cfoutput>
-
- You have #friends.recordCount# friends!<br />
-
- <!---
- Loop over the friends, but output them by adjacent gender
- (using the GROUP attribute). This way, we can output the
- Women and then the Men.
- --->
- <cfloop query="friends" group="gender">
-
- <br />
- #friends.gender#:
- <br />
-
- <!---
- Since we are in a Grouped query, we need to run an inner-
- cfloop to output the actual grouped rows. Exclude the
- NAME attribute since we want this CFLoop tag to connect
- with the outer, grouped tag.
- --->
- <cfloop>
-
- - #friends.name#<br />
-
- </cfloop>
-
- </cfloop>
-
- </cfoutput>
NOTE: I am using the enhancements of QueryNew() in ColdFusion 10 to construct the query object manually using an array of row values.
Notice that when I go to use the first CFLoop tag, I am using the [group="gender"] attribute. This tells ColdFusion to group together adjacent rows with the same "gender" column value. Inside this CFLoop tag, I then have another CFLoop tag that executes the group-specific loop. In order for this CFLoop tag to link to the outer, group-wise CFLoop tag, I have to exclude the Name attribute. If I were to include the Name attribute, this inner loop would execute completely independently.
When we run the above code, we get the following, grouped output:
You have 5 friends!
Female:
- Sarah
- Tricia
- Joanna
Male:
- Arnold
- Vin
As you can see, the sibling rows with the same gender value were all lumped into the same group. While this grouping is very limited in behavior (ex. it doesn't offer group-based properties like RowCount or CurrentRow), it can definitely make simple output algorithms extremely easy.
Reader Comments
This is really cool. I didn't realize the output tag grouped function worked that way. I can really see this being useful. Thanks for the post Ben!
Wow, I've been waiting basically a DECADE for this attribute! FINALLY!
@Ryan V,
No problem my man!
@Ryan J,
Ha ha, word up! It has been a super long time in the making :)
The way you're using this feature has been available in ColdFusion for years. In current versions of ColdFusion you can perform this exact action using cfoutput tags instead of cfloop. This entails adding more cfoutput tags than maybe you'd like throughout the page, but the way you're showing this feature isn't really a new feature.
Love the site Ben, just thought I'd point this out in case anyone is unaware of this option in current version of ColdFusion.
Still wish there was a GroupRecordCount
I think the real power of this can be seen nesting cfoutput using group with a cfloop query using its own group nested inside. I have ran into this a time or two and now it looks like a clean solution to my woes has evolved in CF10.....Thanks for the insight Ben
