One of the features Macromedia introduced in ColdFusion 5 was the ability to execute SQL statements against a pre-existing query result set. It called this functionality Query of Queries. With ColdFusion MX, the Query of Queries feature set has been expanded even further. I’ll show you how to use Query of Queries and demonstrate some problems that the feature can easily solve.
Using Query of Queries
Every ColdFusion developer is well acquainted with the <cfquery> tag, which is a database abstraction tag that allows you to execute SQL statements against virtually any database.
Take this simple example:
<cfquery name="getCustomers" datasource="customerDB">
Running the query against a sample database might yield a recordset like the one shown in Figure A.
With Query of Queries, I can execute additional SQL statements against this result set. This example would return the maximum customerID in the getCustomers recordset. Note that the dbtype attribute of <cfquery> is set to query, which informs the CF server that you want to execute a Query of Queries:
<cfquery name="getMaxCustomerID" dbtype="query">
max( customerID ) as maxCustomerID,
Note that Query of Queries doesn't accept the entire ANSI SQL specification. However, it does support many common SQL commands and aggregate functions. The WHERE clause accepts conditionals such as LIKE, IN, and BETWEEN; and Min(), Max(), Avg(), Count(), and Sum() are supported aggregate functions. You're free to use the ORDER BY and GROUP BY clauses, and you can create INNER JOINS, OUTER JOINS, and UNIONS, which opens up some interesting capabilities.
Avoid unnecessary loops with Query of Queries
Returning to our original example query, getCustomers, you see that the result set is ordered by customerState. Supposed I wanted to output this data, grouped by customerState. Let’s further suppose that I don’t want to output the state on every line, but just one time, using a table rowspan to associate one state with many customers. One option might be to loop over the query and calculate how many records there are for each state, and use that count for the rowspan, as in Listing A.
This code will work, and I’d get my desired result, as you can see in Figure B.
However, this could get rather inefficient if the query contained 15,000 records. I’d be looping over all 15,000 rows for every state. A more optimized approach is to use Query of Queries to select a count of records for each state, as shown in Listing B.
The final result is exactly the same as the looping approach, but the code is more readable and the execution time is faster, especially as the number of records increases.
JOIN result sets from different datasources
Another unique use for Query of Queries is to JOIN recordsets from separate queries. By extension, this means you can JOIN recordsets from different datasources as well.
Continuing with our example, let’s assume that my datasource for customers is an Oracle database, but the database for customer orders is SQL Server. I realize this is a bit contrived, but we all know how strange the corporate operating environment can be. Using Query of Queries, I can run a JOIN on the two recordsets.
In this example, I’m grabbing orders for a specific customer. First, we'll look at the getCustomerOrders query, which will provide the second recordset that we’ll use in our JOIN along with the getCustomers recordset:
<cfquery name="getCustomerOrders" datasource="customerOrderDB">
This would produce a recordset like the one shown in Figure C.
Now, let’s JOIN these separate result sets:
<cfquery name="getOrdersForCustomer" dbtype="query">
getCustomerOrders.customerID = getCustomers.customerID
AND getCustomerOrders.customerID = 91
The resulting recordset can be seen in Figure D.
|JOIN Query of Query|
This ability to relate records in separate queries can be a useful approach to certain programming challenges.
Browsing Next N records
A common element of functionality that many applications require is the ability to page through a long recordset. Query of Queries is one approach to handling this need. By combining ColdFusion’s query caching ability with Query of Queries, we can allow a user to page through a recordset while generating only one hit against the database server.
Let’s stick with our existing query getCustomers, modified slightly to cache the query for 10 minutes:
<cfquery name="getCustomers" datasource="customerDB" cachedwithin="#createTimeSpan( 0,0,10,0 )#">
order by customerState
We can show the user the first two records like this:
<cfset recordsPerPage = 2>
<cfparam name="url.lastCustomerIDViewed" default="0">
<cfquery name="getNextCustomers" maxrows="#recordsPerPage#" dbtype="query">
where customerID > #url.lastCustomerIDViewed#
We get the first two rows of the customer query back, IDs 4 and 19. From here, we can pass the customerID of the last record viewed (19) back to the page, and the Query of Queries will return the next two records. So the next two records returned would be 22 and 29. With a bit of additional logic, we could handle users who want to page backwards through the record set as well.
This approach does have one limitation: What happens if your recordset has a million rows in it? Caching the entire thing and paging through it this way would be inefficient. However, for recordsets of moderate size (say up to 20,000 records), this approach is a fast and simple way to allow Next N record browsing.