Forta.com - Blog

CFOUTPUT makes grouping nested result sets really easy, just use the GROUP attribute to specify the column to group by, and then nest CFOUTPUT tags as needed (and multiple levels of grouping are supported, too). This example will display dept each time that value changes, and then create an unordered list of users for each:

<cfoutput query="users" group="dept">
   <h3>#dept#</h3>
   <ul>
      <cfoutput>
         <li>#username#</li>
      </cfoutput>
   </ul>
</cfoutput>

CFOUTPUT also makes displaying partial result sets really easy, just use STARTROW to specify the row to start from, and MAXROWS to specify the maximum number of rows to display. Here's a simple example:

<cfoutput query="users" startrow="1" maxrows="25">
<li>#username#</li>
</cfoutput>

Now, what would happen if you combined GROUP with STARTROW and MAXROWS, like this?

<cfoutput query="users" group="dept" startrow="1" maxrows="25">
   <h3>#dept#</h3>
   <ul>
      <cfoutput>
         <li>#username#</li>
      </cfoutput>
   </ul>
</cfoutput>

You'd probably expect STARTROW and MAXROWS to apply to the entire result set, so that if you had 5 departments with 10 users in each (50 rows total) the first 25 rows would be displayed (all users in the first 2 departments and 5 of the users in the third). But, nope, it does not work that way. STARTROW and MAXROWS do their calculations based on how many times their CFOUTPUT is invoked, and so when used with GROUP, STARTROW and MAXROWS apply to the outer group. In this example, the first 25 groups would be included, and thus all 50 users.

I am not sure that I actually agree with this behavior, but that's the way it is. In practice, this means that if you do need to use GROUP together with STARTROW and MAXROWS, then you'll probably want to copy the subset of query data to be used to another query first, and pass that second subset query to CFOUTPUT.

Comments

[Add Comment]

Yep, I've run into this issue a lot of times. I tend to stay away from the group attribute within cfoutput in favor of grouping at the database level. It's still useful in some situations but paging a grouped outpuit isn't fun at all.

# Posted By Dave Cordes | 2/12/07 10:01 AM

Grouping also affects attempts at alternating row background colors. My work around has been to set a counter and increment it by one on the inner-most nested cfoutput. Then use the counter for alternating colors.

I've also had to do a work around for cfoutput / groupby and attaching a javascript sortable feature. In this case I ended up using cfsavecontent to temporarily store all my output then put that query output into a table that the javascript can attach itself too.

# Posted By Matt Williams | 2/12/07 11:10 AM

Not sure why this would be considered unexpected behavior in any way. When you set the group attribute, you instantly change the type of row you are displaying to a nested iteration, so it's only logical that it would display all the rows in your example. In fact, this follows the very same commonly used logic of loop nesting used in most languages, including ColdFusion.

# Posted By RF | 2/12/07 8:12 PM

What about recordcount or currentrow? If they do not observe the group argument they could provide support for paging/alternating row colors without to much hassle. Also wondering how those arguments (maxrows/startrow) affect the inner cfoutput. I guess a little demo script is in order.

# Posted By Mike Vierow | 2/12/07 8:26 PM

A caveat: in CF 5, the maxrows attribute works as described in this post, but the startrow attribute still applies to the full result set. Does anyone know if this is the case for MX as well?

# Posted By CM | 2/14/07 12:45 PM

This is a great example of 2 levels of nesting. Can you please show an example of 3 or 4 levels on nesting?

# Posted By Nate | 3/2/07 2:12 PM

Nate, I think you can nest cfoutputs with the group attribute set on the nested output tags. e.g. for 3 level nesting:
<cfoutput query="myquery" group="genre">
#genre#
<cfoutput group="author">
#author#
<cfoutput>
#title#
</cfoutput>
</cfoutput>
</cfoutput>

# Posted By CM | 3/2/07 2:58 PM

[Add Comment]

CFOUTPUT Grouping Gotcha