Having trouble grouping CFQuery results

Question

I have a CFQuery to pull data from a table which I would like to output to the screen in a format that groups the data according to one of the columns, 'Company Name'. I can't seem to wrap my head around the logic for this.

Currently I'm just looping through the data to output it to the screen and separating it by a horizontal rule tag. Not the best looking way to do it and it generates a really long list of results that the user needs to scroll through. I am hoping that by grouping the data, it will be more readable.

Here is my code:

<!--- Feedback Query --->
  <cfquery name="getFeedback" datasource="#datasource#">
    select ticket_id, service_satisfaction, customer_notes, response_date, company_name
    from service_verification
    order by company_name
  </cfquery>

        <br />
        <cfoutput query="getFeedback" group="company_name"><strong>Company Name: #getFeedback.company_name#</strong><br />

          <cfquery dbtype="query" name="parsed">
          select company_name
          from getFeedback
          where company_name = '#getFeedback.company_name#'
          </cfquery>
          (#parsed.recordcount# Responses)<br />

          <cfoutput>


          Ticket Number: <cfif #getFeedback.ticket_id# eq 0>No ticket associated with this feedback. This was solicited feedback.<cfelse>#getFeedback.ticket_id#</cfif><br />
          Date: #DateFormat(getFeedback.response_date, 'mm/dd/yyyy')# at #TimeFormat(getFeedback.response_date, 'hh:mm:ss')#<br /><br />
          Rating: <cfif #getFeedback.service_satisfaction# eq 'thumbs-up'><img src="images/thumbs-up-small.png" /><cfelse><img src="images/thumbs-down-small.png" /></cfif><br />
          Customer Notes: <cfif #getFeedback.customer_notes# eq ''>No additional comments provided.<cfelse>#getFeedback.customer_notes#</cfif><br /><br />
          <hr style="border-top: 1px dashed ##8c8c8c;" />
          </cfoutput>

        <br />
    </cfoutput>

Here is a sample of my CFDUMP from the above query:

company_name        service_satisfaction    response_date               ticket_id   customer_notes  
1   AmerTech        thumbs-up               {ts '2014-10-22 10:25:14'}  22667       Jeff was great. thanks  
2   AmerTech        thumbs-up               {ts '2015-01-20 12:02:34'}  23795       Rich was good. Thanks. He needs to send out a another drive that we would like as backup to take home at night. Also, he missed one machine for backups that I need to discuss. Have someone please call . Thanks 
3   AmerTech, Inc   thumbs-up               {ts '2015-04-16 13:56:44'}  25066
4   AmerTech, Inc   thumbs-down             {ts '2015-10-22 11:23:40'}  27293       Brian, I understand from Dave that you could not solve the problem and that he had to call the OEM to solve the problem. This is what I was informed. I do not know any of the details surrounding the issue. but it shouldn't take that long to install a printer on a new laptop. Why did this occur and how do I make sure it doesn't happen again. thanks mark
5   AMIB            thumbs-down             {ts '2014-10-02 12:18:27'}  22463       Representative did not call me upon arrival at group home as instructed and implemented changes without approval from HR
6   AMIB            thumbs-up               {ts '2015-06-08 09:58:03'}  25599
7   AMIB            thumbs-up               {ts '2016-03-10 14:10:01'}  28777
8   AMIB            thumbs-up               {ts '2016-03-28 09:10:37'}  29193       Michael is a great tech! Extremely helpful and responsive to our needs! 
9   AMIB            thumbs-up               {ts '2016-03-28 10:19:19'}  28777

Update:

When I add the group attribute to the cfoutput tag, it only shows the first result from the group

<cfloop query="getFeedback"> 

  <cfoutput><cfoutput query="getFeedback" group="company_name">

      Company Name: #getFeedback.company_name# &nbsp; <cfif #getFeedback.service_satisfaction# eq 'thumbs-up'><img src="images/thumbs-up-small.png" /><cfelse><img src="images/thumbs-down-small.png" /></cfif><br />
      Ticket Number: <cfif #getFeedback.ticket_id# eq 0>No ticket associated with this feedback. This was solicited feedback.<cfelse>#getFeedback.ticket_id#</cfif><br />
      Date: #DateFormat(getFeedback.response_date, 'mm/dd/yyyy')# at #TimeFormat(getFeedback.response_date, 'hh:mm:ss')#<br /><br />

      Customer Notes: #getFeedback.customer_notes# &nbsp; <br /><br />

      <br />

  <hr></cfoutput>
  </cfloop>

You are sorting the results by company, which is good, but the output code is missing the "group" attribute. [See outline here](http://stackoverflow.com/questions/24147088/how-to-output-table-results-by-using-cfoutput-group-by-date/24147305#24147305). — Leigh, Sep 13 '16 at 15:23
When I add the group attribute to the cfoutput tag, it only shows the first result from the group. I've updated my original post with the new code. — Brian Fleishman, Sep 13 '16 at 16:20
Take another look at the example in the link. You are using it incorrectly. The group goes on the *outside* cfoutput tag. Also, not sure why you are mixing cfloop and cfoutput. Just use nested cfoutput tags. — Leigh, Sep 13 '16 at 16:31
Ah, Okay it seems to showing the output I desire now (Updated code is posted). I would like to also show the amount of records in each group. In my example code my attempt at it is is by using (#getFeedback.recordcount# Responses). Using the normal #query.recordcount# show to total amount of records for the query and not the group itself. How can that be accomplished? — Brian Fleishman, Sep 13 '16 at 17:09
Not sure what search phrases you used, but a quick search turned up this thread ;-) http://stackoverflow.com/questions/26798463/how-to-find-the-nested-cfoutput-recordcount-when-using-group . In short, you have to DIY with a counter variable. — Leigh, Sep 13 '16 at 17:34
FYI, rolled back edits to preserve the question context. Otherwise, the comments no longer make sense ;-) Go ahead and post the final code as an "answer", rather than as an edit to the question. That way it is more visible to the next guy with the same problem :) — Leigh, Sep 13 '16 at 17:38
Oh... I did not notice the query within the loop. Definitely avoid those if possible, because they are inefficient and do not scale well. Depending on your dbms, you *might* be able to calculate the counts in SQL. If not, try the counter trick mentioned in the other link http://stackoverflow.com/a/26802131/104223 — Leigh, Sep 13 '16 at 17:49

Jules · Answer 1 · 2016-09-14T15:29:10.187

Get rid of the loop tag. Then use this:

<cfoutput query="getFeedback" group="company_name">
    <!--- OUTPUT EACH GROUP --->
    Company Name: #getFeedback.company_name# &nbsp; <cfif #getFeedback.service_satisfaction# eq 'thumbs-up'><img src="images/thumbs-up-small.png" /><cfelse><img src="images/thumbs-down-small.png" /></cfif><br />
    <cfoutput>
        <!--- OUTPUT EACH RECORD --->
        Ticket Number: <cfif #getFeedback.ticket_id# eq 0>No ticket associated with this feedback. This was solicited feedback.<cfelse>#getFeedback.ticket_id#</cfif><br />
        Date: #DateFormat(getFeedback.response_date, 'mm/dd/yyyy')# at #TimeFormat(getFeedback.response_date, 'hh:mm:ss')#<br /><br />      
        Customer Notes: #getFeedback.customer_notes# &nbsp; <br /><br />        
        <br />
    </cfoutput>
    <hr>
</cfoutput>

It's the second/nested CFOUTPUT tag that goes through each record. Note the HR is within the GROUP, not each record.

If you want to get fancy, you can make this an accordion with jQuery. Each GROUP is the head, and each record is the contents.

score 0 · Answer 2 · edited Sep 13 '16 at 18:00

Here is my final verson of the working code. Thanks @Leigh

<!--- Feedback Query --->
<cfquery name="getFeedback" datasource="#datasource#">
     select ticket_id, service_satisfaction, customer_notes, response_date, company_name
     from   service_verification
     order by company_name
</cfquery>

<br />
<cfoutput query="getFeedback" group="company_name">
    <strong>Company Name: #getFeedback.company_name#</strong><br />

    <cfquery dbtype="query" name="parsed">
         select company_name
         from   getFeedback
         where  company_name = '#getFeedback.company_name#'
    </cfquery>
    (#parsed.recordcount# Responses)<br />

    <cfoutput>
         Ticket Number: <cfif getFeedback.ticket_id eq 0>No ticket associated with this feedback. This was solicited feedback.<cfelse>#getFeedback.ticket_id#</cfif><br />
         Date: #DateFormat(getFeedback.response_date, 'mm/dd/yyyy')# at #TimeFormat(getFeedback.response_date, 'hh:mm:ss')#<br /><br />
         Rating: <cfif getFeedback.service_satisfaction eq 'thumbs-up'><img src="images/thumbs-up-small.png" /><cfelse><img src="images/thumbs-down-small.png" /></cfif><br />
         Customer Notes: <cfif getFeedback.customer_notes eq ''>No additional comments provided.<cfelse>#getFeedback.customer_notes#</cfif><br /><br />
         <hr style="border-top: 1px dashed ##8c8c8c;" />
     </cfoutput>

     <br />
</cfoutput>

Having trouble grouping CFQuery results

2 Answers2