Query of Queries

Share this article

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

What is the main purpose of using Query of Queries in ColdFusion?

The primary purpose of using Query of Queries in ColdFusion is to manipulate, filter, and sort data retrieved from a database. It allows developers to perform SQL operations on existing query objects without making additional trips to the database. This can significantly improve the performance of your application, especially when dealing with large amounts of data.

How does Query of Queries differ from regular SQL queries?

Unlike regular SQL queries that interact directly with the database, Query of Queries operates on query objects that are already in memory. This means that you can perform additional operations on your data after it has been retrieved from the database, without the need for further database interactions.

Can I use all SQL functions in a Query of Queries?

While Query of Queries supports many standard SQL functions, there are some limitations. For example, you cannot use aggregate functions like COUNT, SUM, AVG, MAX, and MIN in the WHERE clause of a Query of Queries. Also, JOIN operations are not supported.

How can I optimize the performance of my Query of Queries?

To optimize the performance of your Query of Queries, try to limit the amount of data you retrieve from the database in the first place. The less data you have to manipulate, the faster your Query of Queries will run. Also, avoid using complex expressions in the WHERE clause, as they can slow down the execution of your query.

Can I use Query of Queries with any database?

Yes, Query of Queries is a feature of ColdFusion, not the underlying database. This means that you can use it with any database that ColdFusion supports, including MySQL, Oracle, SQL Server, and others.

What are some common use cases for Query of Queries?

Query of Queries is particularly useful when you need to perform multiple operations on your data, such as filtering, sorting, and grouping. It’s also handy when you need to combine data from multiple queries, or when you want to create a new query based on the results of a previous one.

Can I use Query of Queries to update my data?

No, Query of Queries is a read-only operation. You cannot use it to insert, update, or delete data in your database. If you need to modify your data, you will need to use a regular SQL query.

How do I debug a Query of Queries?

Debugging a Query of Queries can be a bit tricky, as the error messages are often not very descriptive. However, a good starting point is to make sure that your SQL syntax is correct and that all the columns you are referencing actually exist in your query object.

Can I use Query of Queries in a loop?

Yes, you can use Query of Queries inside a loop. However, keep in mind that each Query of Queries operation has a certain overhead, so if you’re dealing with a large number of iterations, it might be more efficient to use a different approach.

Are there any security concerns with using Query of Queries?

As with any SQL operation, it’s important to be aware of the risk of SQL injection attacks. However, since Query of Queries operates on in-memory data rather than directly interacting with the database, the risk is somewhat mitigated. Nevertheless, you should always validate and sanitize your input data to ensure its safety.

Eric JonesEric Jones
View Author
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form