Ben Forta

AdobeStock_455007340

CFOUTPUT Grouping Gotcha

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:

#dept#

  • #username#


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:

  • #username#


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

    #dept#

    • #username#


    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.

    10 responses to “CFOUTPUT Grouping Gotcha”

    1. Dave Cordes Avatar
      Dave Cordes

      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.

      Reply
    2. Matt Williams Avatar
      Matt Williams

      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.

      Reply
    3. RF Avatar
      RF

      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.

      Reply
    4. Mike Vierow Avatar
      Mike Vierow

      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.

      Reply
    5. CM Avatar
      CM

      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?

      Reply
    6. Nate Avatar
      Nate

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

      Reply
    7. CM Avatar
      CM

      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>

      Reply
    8. Kathy Avatar
      Kathy

      You are the best! I have been banging my head trying to do a nice grouping on a simple web page and have spent several worthless days trying to accomplish it. You saved me!! I have your book from ColdFusion 5 and it has been my bible — I could not have done most of my slick pages without you!!

      Reply
    9. Amanda Avatar
      Amanda

      Is there a way to group but have the listed items be in alphabetical order?

      Reply
    10. Annie Avatar
      Annie

      As in the above first example, what would happen if we dont use the group attribute like this?
      <cfoutput query="users" >
      <h3>#dept#</h3>
      <ul>
      <cfoutput>
      <li>#username#</li>
      </cfoutput>
      </ul>
      </cfoutput>

      Reply

    Leave a Reply