How to find the nested cfoutput recordcount when using group

Question

Consider the following:

<cfoutput query="resources" group="type">
    <h4>#type#</h4>
       <cfoutput>
          #name#
       </cfoutput>
 </cfoutput>

resources.recordcount would give me the total number of records, but is there an elegant way of finding out the recordcount of the nested data? e.g

<cfoutput query="resources" group="type">
    <h4>#type# (#noofrecords# of #resources.recordcount#)</h4>
       <cfoutput>
          #name#
       </cfoutput>
 </cfoutput>

I could probably do something hacky with loops, but wondered if there was a way of doing it using the cfoutput groups specifically.

score 8 · Answer 1 · answered Nov 07 '14 at 13:16

8

You could do an output before to get the count. This would be more efficient than doing a query of queries.

<cfoutput query="resources" group="type">
  <cfset noofrecords= 0>
  <cfoutput>
    <cfset noofrecords++>
  </cfoutput>
  <h4>#type# (#noofrecords# of #resources.recordcount#)</h4>
  <cfoutput>
    #name#
  </cfoutput>
</cfoutput>

answered Nov 07 '14 at 13:16

Matt Busche

14,216
5
36
61

heh :) - ok, that's actually really sneaky. – Neokoenig Nov 07 '14 at 21:57

score 6 · Accepted Answer · answered Nov 07 '14 at 10:03

6

I am afraid that you would have to do some counting yourself. There is no RecordCount for the nested grouped output, because it is really all the same query, CF is just doing a little formatting for you.

answered Nov 07 '14 at 10:03

Tushar Bhaware

2,525
1
16
29

Sadly, it seems this is the correct answer! back to loop + QoQ then. – Neokoenig Nov 07 '14 at 12:04
1

That's what I though too, until cfqueryparam showed up. – Dan Bracuk Nov 07 '14 at 18:03
@Neokoenig - That does not mean you need a QoQ. A counter and nested loop would do the same thing. Not as elegant as query.recordCount, but it gets the job done without the unnecessary o/h of a bunch of QoQ's. – Leigh Nov 07 '14 at 18:52

John Whish · Answer 3 · 2014-11-07T10:48:40.543

You could get the count in the original query if you wanted an alternative method, but, you will need to test the performance before you go down this route (although using Query of Queries can also have performance issues as there are no indexes).

SELECT f1.id, f1.name, f1.type, f2.typeCount
FROM foo f1
INNER JOIN (
    SELECT COUNT(type) as typeCount, type
    FROM foo
    GROUP BY type 
) f2 on f1.type = f2.type 
ORDER BY f1.type, f1.name

Then in the CF you can do:

<cfoutput query="resources" group="type">
    <h4>#resources.type# (#resources.typeCount# of #resources.recordCount#)</h4>
    <cfoutput>
      #resources.name#
    </cfoutput>
</cfoutput>

As a side note, in CF10 you can also group inside a cfloop with query like so:

<cfloop query="resources" group="type">
    <h4>#resources.type# (#resources.typeCount# of #resources.recordCount#)</h4>
    <cfloop>
      #resources.name#
    </cfloop>
</cfloop>

Thanks for the thought - the ORM I'm using would mean I've got to break out into cfquery - it's what I was really trying to avoid. — Neokoenig, Nov 07 '14 at 12:06

Regular Jo · Answer 4 · 2014-11-07T22:50:22.250

Another solution is:

<!--- I chose the pipe delimiter | names of things often contain commas,
      otherwise, use any delimiter you like --->
<cfset TypesList = Valuelist(resources.type,"|")>

<cfoutput query="resources">
  <h4>#Type# (#ListValueCount(TypesList,Type,"|")# of #resources.recordcount#)</h4>
  <cfoutput>#name#</cfoutput>
</cfoutput>

While this will also work for other applications, it won't work for everything. It expects the Category count ('Type' here) to be unique (no two types with the same name). A better way to handle this is to count based on an ID rather than a name. You might have, for instance, a category setup like this

Fruits
  Red Ones
    Strawberries
    Raspberries
  Yellow Ones
    Bananas
Vegetables
 Green ones
   Green peppers
 Red Ones
   Red Peppers
   Tomatoes

Based on Type (as a string), the output would say Red Ones (4), Yellow Ones (1), Green Ones (1), Red Ones (4), but in a category-products relational table structure, coutning based on unique ID of the category would retrieve accurate counts.

@Neokoenig Hey no problem, I did realize that I forgot to include the | delimiter for the ListValueCount(). Fixed that. Anyway, glad to help. — Regular Jo, Nov 07 '14 at 22:51

score -1 · Answer 5 · answered Nov 07 '14 at 10:31

-1

To get the count of the nested data and display it where you say you want it, I would do this:

<cfoutput query = "rescources" group = "type">
query of queries to get the count this type
output the type and the count
<cfoutput>
output nested data
</cfoutput>
</cfoutput>

answered Nov 07 '14 at 10:31

Dan Bracuk

20,699
4
26
43

Thanks for the thought - I was trying to see if there was a way of doing it without QoQ etc, apparently not. – Neokoenig Nov 07 '14 at 12:05
1

@Neokoenig take a look at my answer. no need for QoQ. – Matt Busche Nov 07 '14 at 13:17
Sorry, but -1 for the QoQ within a loop. Not the cheapest way to calculate a count. – Leigh Sep 14 '16 at 01:54

How to find the nested cfoutput recordcount when using group

5 Answers5

Linked