Conditional CF Record Count

Question

I'm trying to save my database server a few requests by querying a large number of items from several categories all at once, then using <cfif ... > statements to filter those results into unique tables I'm showing for each category. I'm looking to find record counts for each of the categories returned, not just the record count of the overall query.

The basic code is:

<cfinvoke component="..." method="..." returnvariable="session.queryList">
    ...
</cfinvoke>

<cfoutput #session.queryList#>
    <cfif #category# eq "A">
        [Table for A things]
    </cfif>
    <cfif #category# eq "B">
        [Table for B things]
    </cfif>
    <cfif #category# eq "C">
        [Table for C things]
    </cfif>
</cfoutput>

I don't want to use "ORDER BY category" here because the tables are actually on different divs we're hiding and showing, so we need separate tables.

The problem I'm running into is that I want the "Table for A Things" to say "No results" if there is no records returned where category="A", but RecordCount seems to apply to the entire query. Is there any way to say something along the lines of <cfif #queryList.RecordCount# WHERE #category# eq "A" GT "0">?

QoQ's work, but are less ideal. Keep in mind they're not free. So you're basically trading extra db trips for extra resource cost to build multiple datasets in memory. I think it's possible to do it within the original sql query. 1. What's your dbms? 2. What does the "big" query look like? — SOS, Jun 26 '18 at 15:44

score 3 · Accepted Answer · answered Jun 25 '18 at 15:53

QoQ can help.

<cfinvoke component="..." method="..." returnvariable="session.queryList">
 ...
</cfinvoke>
 <!---then run QoQ on it--->
<cfquery name="catA" dbtype="query">
 select * from session.queryList where category ="A"
</query>
<cfquery name="catB" dbtype="query">
 select * from session.queryList where category ="B"
</query>
<cfif catA.recordcount>
 <cfoutput query="catA">
  [Table for A things]
 </cfoutput>
 <cfelse>
  No Records for A things
</cfif>
<cfif catB.recordcount>
 <cfoutput query="catB">
  [Table for B things]
 </cfoutput>
 <cfelse>
  No Records for B things
</cfif>

That's something I was thinking of before, but I must have messed something up because it didn't work correctly. Thank you for the suggestion and the proper formatting, it'll really help. — MLynch, Jun 25 '18 at 16:02

score 1 · Answer 2 · answered Jun 25 '18 at 15:24

1

I believe you are trying to do the following. <cfoutput> has a feature that helps you group the query results given the query is ordered by the grouping item.

<cfoutput query="#session.queryList#" group="category">
  <h3>Category #category#</h3>
  <table>
    <tr><th>...</th><th>...</th></tr>
    <cfoutput><!--- This cfoutput loops through the each record in the group. --->
      ---rows---
    </cfoutput>
  <table>
</cfoutput>

answered Jun 25 '18 at 15:24

rrk

15,677
4
29
45

I'm not quite trying to do grouping like that. I'm trying to take the query return, filter it so that just certain returned records are shown in each specific table, and get a record count for those filtered results. So if I have 500 total records returned, and 100 of them are Category A, I want a way to say "if recordCount for category A is 0, show 'No items of Category A," but I can only figure out how to get the total query's record count. – MLynch Jun 25 '18 at 15:47
@MLynch - Think you could still do that with a grouped output. Just set a flag to indicate when there are no "things" found results. https://trycf.com/gist/f15f95f588ac3271d0a52c6a493b2b05/lucee5?theme=monokai – SOS Jun 25 '18 at 20:24

score 0 · Answer 3 · answered Jun 26 '18 at 13:43

0

QofQ is slow. You can accomplish this with a single round trip to mySQL:

SELECT someColumn, category, count(*) AS categoryCount 
FROM theTable
GROUP BY category
ORDER BY category, someColumn

Grouping will give you a count per category which you can use in CFML.

<cfoutput query="session.queryList" group="category">
    <cfif categoryCount eq 0>
        No Records for #category#. =(
    </cfif>
    <cfoutput>
        #someColumn#<br>
    </cfoutput>
</cfoutput>

answered Jun 26 '18 at 13:43

Jules

1,941
15
18

That's an interesting thought, and could work with some additional logic. It won't work as is, since the `count(*)` in that query will never be 0. @MLynch - Which dbms are you using? What's is the query used to return multiple resultsets? – SOS Jun 26 '18 at 15:48
Also, depending on the DBMS, you can't `GROUP BY` fewer columns than you are `SELECT`ing. But you can use a subselect or a window function to get the counts you're looking for. – Shawn Jun 27 '18 at 14:49

Conditional CF Record Count

3 Answers3