Search code examples
coldfusioncfquerycfoutput

Conditional CF Record Count

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">?

Solution

  • 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>