ColdFusion has two primary tags that allow for explicit iteration over a query: CFOutput and CFLoop. While I appreciate that CFOutput allows for query iteration, I've pretty much never used it to that extent. To me, CFOutput has really only ever been a tag that processes output. As such, I throw one at the top, one at the bottom, and then I try to make it rain in between. People who do make use of CFOutput as a query iterator, however, often lament the fact that there is one huge difference between CFOutput and CFLoop: the "Group" attribute. While the Group attribute is nice for implicit grouping, I find that ColdFusion query-of-queries provide more flexibility and functionality, even when dealing with the most simple data sets.
The Group attribute of the CFOutput tag allows you to implicitly iterate over a sub-set of sibling records that have the same value in a given column (the "group" column). In such a situation, a nested CFOutput tag provides the inner loop for each group. To see what I'm talking about, take a look at this code that iterates over singers and then their songs:
<!--- Query for all the songs - singer data will be replicated across the records. ---> <cfquery name="allSongs" datasource="testing"> SELECT s.id, s.singerID, s.name, <!--- Get artist information. ---> ( sgr.name ) AS singerName FROM song s INNER JOIN singer sgr ON s.singerID = sgr.id ORDER BY singerName ASC, name ASC </cfquery> <!--- Loop over all songs (group by singer name). ---> <cfoutput query="allSongs" group="singerName"> <!--- Output the singer for THIS group. ---> #allSongs.singerName#:<br /> <!--- Loop over all songs in this group (by singer name). ---> <cfoutput> -- #allSongs.currentRow#) #allSongs.name#<br /> </cfoutput> <br /> </cfoutput>
As you can see here, we have a query that contains a cross-product of songs and singers. We are then outputting the songs using CFOutput and the group column, "singerName". Running this code, we get the following output:
-- 1) Mr. Brown
-- 2) Simmer Down
-- 3) Stir It Up
-- 4) I Can't Make You Love Me
-- 5) Something To Talk About
-- 6) About Mr. Brown
-- 7) Heard The World
-- 8) Hey Girl
-- 9) If Only She Knew
As you can see, this makes group-based iteration really easy. Sure, the numbers next to the songs don't necessarily make sense; but, the code needed in order to produce this output was rather trivial.
So, if this is so easy, why do people ever use the CFLoop tag and then lament the lack of the Group attribute? Well, a lot of the time, using the CFOutput tag to output a query is simply not convenient. The CFoutput tag is used to process output in general. As such, we are often faced with situations in which a query loop is already in the context of a CFOutput tag. And, as anyone who has tried to use one CFOutput tag within another, you're well aware that it raises the following ColdFusion exception:
Invalid tag nesting configuration. A query driven cfoutput tag is nested inside a cfoutput tag that also has a query attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing.
In such a situation, a ColdFusion query-of-queries can be used to produce the same grouped output. Now, as much as this might seem like the "fall-back" choice, I would argue that the query-of-query approach is actually much more enjoyable.
When we use the CFOutput Group attribute, we do get implicit sub-grouping. But, we get nothing else. There's no greater sense of the group itself - no group-based index, no group-based record count. If we wanted to know anything about the group at all, or about the number of groups, we'd have to start adding our own logic. The query-of-query approach, on the other hand, requires more code for sure, but is, in my opinion, almost always a more enjoyable, flexible, powerful option.
Let's take the same example as above but generate the output using ColdFusion query-of-queries:
<!--- Query for all the songs - singer data will be replicated across the records. ---> <cfquery name="allSongs" datasource="testing"> SELECT s.id, s.singerID, s.name, <!--- Get artist information. ---> ( sgr.name ) AS singerName FROM song s INNER JOIN singer sgr ON s.singerID = sgr.id ORDER BY singerName ASC, name ASC </cfquery> <!--- Query for all singers. ---> <cfquery name="singers" dbtype="query"> SELECT ( singerID ) AS id, ( singerName ) AS name FROM allSongs GROUP BY id, name ORDER BY name ASC </cfquery> <cfoutput> <!--- Loop over all the singers - for each singer we will re-query the data for the songs sung by the given singer. ---> <cfloop query="singers"> <!--- Query for the songs sung by the current singer. ---> <cfquery name="songs" dbtype="query"> SELECT id, name FROM allSongs WHERE singerID = #singers.id# ORDER BY name ASC </cfquery> #singers.name# [#songs.recordCount# Songs]:<br /> <!--- Output things singer's songs. ---> <cfloop query="songs"> -- #songs.currentRow#) #songs.name#<br /> </cfloop> <br /> </cfloop> </cfoutput>
So, immediately you probably notice that this code is about twice as long. But, if you can detach yourself from the notion that lines of code is an indicator of quality, perhaps you can enjoy the fact that the code is much more insightful. Not only do we get the same grouping as above; but, this time, we know how many singers there are, how many songs each artist has sung, and we can properly number the songs within the sub-groups:
Bob Marley [3 Songs]:
-- 1) Mr. Brown
-- 2) Simmer Down
-- 3) Stir It Up
Bonnie Raitt [2 Songs]:
-- 1) I Can't Make You Love Me
-- 2) Something To Talk About
O.A.R. [4 Songs]:
-- 1) About Mr. Brown
-- 2) Heard The World
-- 3) Hey Girl
-- 4) If Only She Knew
There's no doubt that the CFOutput / Group attribute combination is useful. I think in a small set of problems and contexts, it really provides the perfect, low-hanging-fruit solution. But, given the problem with nesting CFOutput tags and the limited insight of the group itself, I personally find that a ColdFusion query-of-queries provides more value more often. At the very least, it's good to know that there are multiple ways to solve the same grouping problem when both options are not simultaneously viable.
Want to use code from this post? Check out the license.