ColdFusion 10 - Using The Group Attribute With CFLoop To Group Query Rows

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:

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.

You Might Also Be Interested In: