ColdFusion Query Of Queries Odd COUNT(*) Behavior
Posted September 7, 2006 at 10:59 AM by Ben Nadel
If you ever run a COUNT(*) aggregate in a SQL SELECT statement, you will know that it returns the number of records that match your select criteria. If 14 records match, it returns 14. If zero records match, it returns zero. This behavior, however, is corrupted when it comes to ColdFusion query of queries.
As always, let's start by building a query against which to run tests:
- <!--- Set up a query for girls. --->
- <cfset qGirls = QueryNew( "id, name, hotness, hair" ) />
-
- <!--- Add some rows to the query. --->
- <cfset QueryAddRow( qGirls, 4 ) />
-
- <!--- Set data values. --->
- <cfset qGirls[ "id" ][ 1 ] = 1 />
- <cfset qGirls[ "name" ][ 1 ] = "Libby" />
- <cfset qGirls[ "hotness" ][ 1 ] = 8.5 />
- <cfset qGirls[ "hair" ][ 1 ] = "Brunette" />
-
- <cfset qGirls[ "id" ][ 2 ] = 2 />
- <cfset qGirls[ "name" ][ 2 ] = "Yuu" />
- <cfset qGirls[ "hotness" ][ 2 ] = 7.5 />
- <cfset qGirls[ "hair" ][ 2 ] = "Black" />
-
- <cfset qGirls[ "id" ][ 3 ] = 3 />
- <cfset qGirls[ "name" ][ 3 ] = "Kim" />
- <cfset qGirls[ "hotness" ][ 3 ] = 7.5 />
- <cfset qGirls[ "hair" ][ 3 ] = "Brunette" />
-
- <cfset qGirls[ "id" ][ 4 ] = 4 />
- <cfset qGirls[ "name" ][ 4 ] = "Molly" />
- <cfset qGirls[ "hotness" ][ 4 ] = 10.0 />
- <cfset qGirls[ "hair" ][ 4 ] = "Brunette" />
Now that we have our girls set up, let's query our query to find the number hot girls we have. For this test, our definition of hot will be those girls with a hotness rating of 8 or higher.
- <!---
- Now, get the number of girls that have a hotness
- over 8. That's really hot.
- --->
- <cfquery name="qHotGirls" dbtype="query">
- SELECT
- COUNT( * ) AS girl_count
- FROM
- qGirls
- WHERE
- hotness > 8
- </cfquery>
As you can see, all we are doing is selecting the COUNT( * ) on the query. Dumping out the results, we find we have two hot girls, just as can be expected:
| | | | ||
| |  | | ||
| | | |
Now, let's query our query to find the number of not-so-hot girls. For this test, our definition of not-so-hot will be those girls with a hotness rating less than 5.
- <!---
- Now, get the number of girls that have a hotness
- under 5. That's not so hot.
- --->
- <cfquery name="qNotSoHotGirls" dbtype="query">
- SELECT
- COUNT( * ) AS girl_count
- FROM
- qGirls
- WHERE
- hotness < 5
- </cfquery>
Again, we are just getting the number of girls as a COUNT aggregate. As we had no not-so-hot girls in the original query, we would expect to get a record with the value zero (0). Dumping out the results, though, you will see that in fact, we have no records at all:
| | | | ||
| |  | | ||
| | | |
This is bizarre and can be a hassle in your programming. Now, just to make sure that I am not going crazy, I am going to do a control test against a regular SQL database to see how it works:
- <cfquery name="qControlTest" datasource="...">
- SELECT
- COUNT( * ) AS record_count
- FROM
- blog_entry
- WHERE
- <!--- This ensures that no records will be returned. --->
- 1 = 0
- </cfquery>
This query is designed to NEVER return anything but zero as the count. And, from the following dump, you will see that this is in fact how a COUNT aggregate in the SQL DB works:
| | | | ||
| |  | | ||
| | | |
Now, as I mentioned, this can be somewhat irritating to deal with in the code. The easiest solution to work with is to simply check to see if any records were returned, and, if no records were returned, create one manually with a default value:
- <!--- Check to see if any records were returned. --->
- <cfif NOT qNotSoHotGirls.RecordCount>
-
- <!--- Set default values for aggregates. --->
- <cfset QueryAddRow( qNotSoHotGirls, 1 ) />
- <cfset qNotSoHotGirls[ "girl_count" ][ 1 ] = 0 />
-
- </cfif>
This way, you can carry on with the programming and not have to take any special considerations just because you were performing a query of queries rather than querying a database.
Reader Comments
nasty example
this might be hugely off topic, but I have made a search form with several dropdown boxes, I would like that if no results match the search (if the query returns no records) the user gets a line of text saying something in the lines of "Your search returned no results, please try changing your criteria", could you possibly let me know how I would go about doing this?
http://petdance.com/2011/07/distracting-examples-ruin-your-presentation/
