When you have to access the same tables multiple times, you greatly reduce access time, because the data is already in memory (in the recordset). A Query of Queries is ideal for tables of 5,000 to 50,000 rows, and is limited only by the memory of the ColdFusion host computer.
Last updated on
Feb 13, 2024
After you have created a recordset with a tag or function, you can retrieve data from the recordset in one or more dependent queries. A query that retrieves data from a recordset is called a Query of Queries. A typical use of a Query of Queries is to retrieve an entire table into memory with one query, and then access the table data (the recordset) with subsequent sorting or filtering queries. In essence, you query the recordset as if it is a database table.
Note:
Because you can generate a recordset in ways other than using the cfquery tag, the term In Memory Query is sometimes used instead of Query of Queries.
Benefits of Query of Queries
Performing a Query of Queries has many benefits, including the following:
-
-
You can perform joins and union operations on results from different data sources. For example, you can perform a union operation on queries from different databases to eliminate duplicates for a mailing list.
-
You can efficiently manipulate cached query results in different ways. You can query a database once, and then use the results to generate several different summary tables.For example, to summarize the total salary by department , by skill, and by job , you can make one query to the database and use its results in three separate queries to generate the summaries.
-
You can obtain drill-down, master-detail information for which you do not access the database for the details.For example, you can select information about departments and employees in a query, and cache the results. You can then display the names of the employees. When users select an employee, the application displays the details of the employees by selecting information from the cached query, without accessing the database.
-
You can use a Query of Queries in report definitions to generate subreport data. For more information, see Using subreports in Common reporting tasks and techniques.
Performing a Query of Queries
Perform a Query of Queries as follows:
-
Generate a recordset through a master query.You can write a master query using a tag or function that creates a recordset. For more information, see Creating a recordset in About recordsets.
-
Write a detail query, a cfquery tag that specifies dbtype="query".
-
In the detail query, write a SQL statement that retrieves the relevant records. Specify the names of one or more existing queries as the table names in your SQL code. Do not specify a datasource attribute.
-
If the database content does not change rapidly, use the cachedwithin attribute of the master query to cache the query results between page requests. This way, ColdFusion accesses the database on the first page request, and does not query the database again until the specified time expires. Use the CreateTimeSpan function to specify the cachedwithin attribute value (in days, hours, minutes, seconds format).
The detail query generates a new query result set, identified by the value of the name attribute of the detail query. The following example illustrates the use of a master query and a single detail query that extracts information from the master.
Use the results of a query in a query
-
Create a ColdFusion page with the following content:
<h1>Employee List</h1> <!--- LastNameSearch (normally generated interactively) ---> <cfset LastNameSearch="Doe"> <!--- Master Query ---> <cfquery datasource="cfdocexamples" name="master" cachedwithin=#CreateTimeSpan(0,1,0,0)#> SELECT * from Employee </cfquery> <!--- Detail Query (dbtype=query, no data source) ---> <cfquery dbtype="query" name="detail"> SELECT Emp_ID, FirstName, LastName FROM master WHERE LastName=<cfqueryparam value="#LastNameSearch#" cfsqltype="cf_sql_char" maxLength="20"></cfquery> <!--- output the detail query results ---> <p>Output using a query of query:</p> <cfoutput query=detail> #Emp_ID#: #FirstName# #LastName#<br> </cfoutput> <p>Columns in the master query:</p> <cfoutput> #master.columnlist#<br> </cfoutput> <p>Columns in the detail query:</p> <cfoutput> #detail.columnlist#<br> </cfoutput>
-
Save the page as query_of_query.cfm in the myapps directory under the web_root.
-
Display query_of_query.cfm in your browser
Note:
If you create a query and select a column, for example, names with order, the order of names may appear incorrect in the output.
Add -Dcoldfusion.application.orderby.caseinsensitive=true in jvm.args to resolve this.
Reviewing the code
The master query retrieves the entire Employee table from the cfdocexamples data source. The detail query selects only the three columns to display for employees with the specified last name. The following table describes the code and its function:
|
Code |
Description |
|---|---|
|
cfset LastNameSearch="Doe" |
Sets the last name to use in the detail query. In a complete application, this information comes from user interaction. |
|
|
Queries the cfdocexamples data source and selects all data in the Employees table. Caches the query data between requests to this page, and does not query the database if the cached data is less than an hour old. |
|
<cfquery dbtype="query" name="detail"> |
Uses the master query as the source of the data in a new query, named detail. This new query selects only entries that match the last name specified by the LastNameSearch variable. The query also selects only three columns of data: employee ID, first name, and last name. The query uses the cfqueryparam tag to prevent passing erroneous or harmful code. |
|
<cfoutput query=detail> |
Uses the detail query to display the list of employee IDs, first names, and last names. |
|
<cfoutput> |
Lists all the columns returned by the master query. |
|
<cfoutput> |
Lists all the columns returned by the detail query. |
Displaying recordset data incrementally
If your database is large, you can limit the number of rows displayed at one time. The following example shows how to do this using the currentRow query variable of a Query of Queries. For more information on query variables, see Getting information about query results.
-
Create a ColdFusion page with the following content:
<html> <head> <title>QoQ with incremental row return</title> </head> <body> <h3>QoQ with incremental row return</h3> <!--- define startrow and maxrows to facilitate 'next N' style browsing ---> <cfparam name = "MaxRows" default = "5"> <cfparam name = "StartRow" default = "1"> <!--- master query: retrieve all info from Employee table ---> <cfquery name = "GetSals" datasource = "cfdocexamples"> SELECT * FROM Employee ORDER BY LastName </cfquery> <!--- detail query: select 3 fields from the master query ---> <cfquery name = "GetSals2" dbtype = "query"> SELECT FirstName, LastName, Salary FROM GetSals ORDER BY LastName </cfquery> <!--- build table to display output ---> <table cellpadding = 1 cellspacing = 1> <tr> <td bgcolor = f0f0f0> <b><i> </i></b> </td> <td bgcolor = f0f0f0> <b><i>FirstName</i></b> </td> <td bgcolor = f0f0f0> <b><i>LastName</i></b> </td> <td bgcolor = f0f0f0> <b><i>Salary</i></b> </td> </tr> <!--- Output the query and define the startrow and maxrows parameters. Use the query variable currentRow to keep track of the row you are displaying. ---> <cfoutput query = "GetSals2" startrow = "#StartRow#" maxrows = "#MaxRows#"> <tr> <td valign = top bgcolor = ffffed> <b>#GetSals2.currentRow#</b> </td> <td valign = top> <font size = "-1">#FirstName#</font> </td> <td valign = top> <font size = "-1">#LastName#</font> </td> <td valign = top> <font size = "-1">#LSCurrencyFormat(Salary)#</font> </td> </tr> </cfoutput> <!--- If the total number of records is less than or equal to the total number of rows, provide a link to the same page, with the StartRow value incremented by MaxRows (5, in this example) ---> <tr> <td colspan = 4> <cfif (startrow + maxrows) lte getsals2.recordcount> <a href="qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow + MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput> rows</a> </cfif> </td> </tr> </table> </body> </html>
-
Save the page as qoq_next_row.cfm in the myapps directory under the web_root.
-
Display qoq_next_row.cfm in your browser.
Using the cfdump tag with query results
As you debug your CFML code, you can use the cfdump tag to quickly display the contents of your query. This tag has the following format:
<cfdump var="#query_name#">
For more information on the cfdump tag, see the CFML Reference.
Using Query of Queries with non-SQL recordsets
A Query of Queries can operate on any CFML tag or function that returns a recordset; you are not limited to operating on cfquery results. You can perform queries on non-SQL recordsets, such as a cfdirectory tag, a cfsearch tag, a cfldap tag, and so on.