Query of Queries

Besides CFCs, one of my favorite aspects of ColdFusion is the Query of Queries, which was introduced in ColdFusion 5.x.

What is a Query of Queries? According to the Macromedia Live Docs,

a query that retrieves data from a record set is called a Query of Queries. After you generate a record set, you can interact with its results as if they were database tables by using Query of Queries.

To me, a Query of Queries is the ability to re-query an existing record set. For example, let’s say you have an interface that shows you all the users in a system. You probably used a query similar to select * from users order by lastName and then output them to your HTML somehow. Now let’s say you list the alphabet across the top of this output so that a user could click on ‘J’, for instance, to see everyone whose last name starts with ‘J’. You would have to re-query the database and use something like select * from users where lastName like 'j%' order by lastName.

The downside to this is that you’ve now sent two queries to your database and taken up that much more bandwidth. For a single request, this is nothing, but can you imagine using these queries on a site that gets thousands of hits a day. Pretty soon, you’re going to be looking at upgrading hardware or your network connection to handle the extra queries and load.

Lightening the Load – the Query of Queries in Action

Now, let’s look at this issue again. This time, though, we’ll use ColdFusion and the Query of Queries ability to lighten the load and increase the functionality of our script. Let’s assume our client has asked for a reporting feature that will show him the following:

• Total number of registered users
• Total number of male users
• Total number of female users
• Total number of male users at or over the age of 18
• Total number of female users at or over the age of 18

We’ll assume our table contains the following columns:

• fname
• lname
• gender
• age
• email

We’ll use this table for all our queries and, since this is just a brief tutorial, we won’t go into details about the data types used or the semantics of SQL queries. In other words, I assume you can create the table, write basic SQL code, and understand basic SQL theory.

Requirement 1: Total Registered Users

Looking at our requirements, the first item the client wants is the total number of registered users. Our SQL will look like this:

<cfquery name="allUsers" datasource="#APPLICATION.dbSource#"> 

  select fname, lname, gender, age, email from users  

</cfquery>

A quick side note here: when I set up an application, I typically store my database connection name in my Application.cfm file and assign it some application-level variable. Feel free to make this fit your coding needs and style.

Now, the above query will grab all the users in the database and return a record set named allUsers. You’ll notice I didn’t do a Select COUNT(*) as count from users query, but instead opted to select all the columns and place this data into the record set. I choose this route because it allows us to further expand the code if, in future, the client wants detailed lists or reports. If we just ran the count query, we’d have to make major modifications for future reports, as the actual user data was missing.

We have a record set that contains all our users. Now, we need to output this information to meet the clients’ request. We need to generate a report that tells the client the total number of users in the system.

Total users in system: <cfoutput>#allUsers.RecordCount#</cfoutput><br />

When placed into a ColdFusion HTML (.cfml or .cfm) page, the above code will generate a line of text showing our client the total number of users currently in the system. This will be displayed as a numerical value that’s automatically calculated and generated by the ColdFusion server as the variable RecordCount.

Requirement 2: Total Male and Total Female Users in the System

Now, we need to generate the output for the total number of male and female users in the system.

As we already have a record set that contains all current users, we will code a Query of Queries to generate the rest of the output. The new code is shown in bold below.

<!--- grab all users ---> 

<cfquery name="allUsers" datasource="#APPLICATION.dbSource#"> 

  select fname, lname, gender, age, email from users  

</cfquery> 

 

<!--- grab all the men ---> 

<cfquery name="allMales" dbtype="query"> 

  select * from allUsers where gender = 'male' 

</cfquery> 

 

<!--- grab all the women ---> 

<cfquery name="allFemales" dbtype="query"> 

  select * from allUsers where gender = 'female' 

</cfquery>

The above code is our first two Queries of Queries. To perform a Query of Queries, you must have a valid record set to query against. For this application, the valid record set is the allUsers query / record set.

Now, we update the output with the newest code, again shown in bold.

Total users in system: <cfoutput>#allUsers.RecordCount#</cfoutput><br /> 

Total male users in system: <cfoutput>#allMales.RecordCount#</cfoutput><br /> 

Total female users in system: <cfoutput>#allFemales.RecordCount#</cfoutput><br />

We need to further query the allMales and allFemales record set so we can report on the total number of users over the age of 18.

Requirement 3: Total Users of Each Gender Over 18

We change our SQL code portions to look like the following (new code in bold):

<!--- grab all users ---> 

<cfquery name="allUsers" datasource="#APPLICATION.dbSource#"> 

  select fname, lname, gender, age, email from users  

</cfquery> 

 

<!--- grab all the men ---> 

<cfquery name="allMales" dbtype="query"> 

  select * from allUsers where gender = 'male' 

</cfquery> 

 

<!--- grab all the men at or over age of 18 ---> 

<cfquery name="allMales18" dbtype="query"> 

  select * from allMales where age >= 18 

</cfquery> 

 

<!--- grab all the women ---> 

<cfquery name="allFemales" dbtype="query"> 

  select * from allUsers where gender = 'female' 

</cfquery> 

 

<!--- grab all the women at over age of 18 ---> 

<cfquery name="allFemales18" dbtype="query"> 

  select * from allFemales where age >= 18 

</cfquery>

Now update your output code as follows:

Total users in system: <cfoutput>#allUsers.RecordCount#</cfoutput><br /> 

Total male users in system: <cfoutput>#allMales.RecordCount#</cfoutput><br /> 

Total male users in system at or over age 18: /<cfoutput>#allMales18.RecordCount#</cfoutput><br /> 

Total female users in system: <cfoutput>#allFemales.RecordCount#</cfoutput><br /> 

Total female users in system at or over age 18: <cfoutput>#allFemales18.RecordCount#</cfoutput><br />

Let’s take a few moments to dive deeper into this code. The first thing you’d have noticed was that our Queries of Queries did not specify a datasource, but instead specified a database type query (dbtype="query"). This tells the ColdFusion engine that we want to re-query an existing record set, thereby creating a Query of Queries. You’ll also notice that, in our select statement, we didn’t reference the table users. Instead, we referenced our original query, which, in the case of our first two Queries of Queries, was allUsers. Note that we don’t specify the genders in the 18 and over searches. This condition was originally stipulated in our allMales and allFemales queries, so it doesn’t need to be applied again.

A Lighter Load

There you have it — a very basic but powerful introduction into the world of Query of Queries.

I’m sure you want to know how this lightens your load. Well, the performance increase is difficult to gauge without a database full of users with whom you can to perform tests. But if you stop and compare what the server must do with our Query of Queries code against the processing it would complete if we performed a new database query for each of these statistics, a performance increase is inevitable. Yes, ColdFusion must spend more time and memory retrieving the complete list of users from the database in the first query, but, in return, it gets to calculate statistics on the fly by simply juggling the record sets in memory, rather than going back to the database, which is always a costly process.

Applying Query of Queries

When should you use Query of Queries? In my humble opinion, Query of Queries should be used when you can limit the number of connections to a database server without hampering the coding and design of your application. I don’t recommend you rush out and run 10 select * from Table statements, using Queries of Queries throughout your application. Keep in mind that the result sets you create have to be stored somewhere. Most of the time, they’re stored in memory (be it RAM or virtual) and, if you don’t plan properly, your server could slow to a crawl or even stop because of low memory resources.

As with any project, it’s best to plan out the entire system first, then go back and refine it multiple times, as you seek ways to increase performance, security, and overall stability. It’s during this refinement phase that you can pinpoint possible areas in which Query of Queries will be beneficial for the applications and the system resources. I hope this article has given you the drive to look over your code and see how you can implement the query of queries functionality into your projects. If you need more help feel free to drop me a line, or post in the SitePoint Forums.

Frequently Asked Questions (FAQs) about Query of Queries