About Query of Queries

After you have created a record set with a tag or function, you can retrieve data from the record set in one or more dependent queries. A query that retrieves data from a record set 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 record set) with subsequent sorting or filtering queries. In essence, you query the record set as if it were a database table.

Note: Because you can generate a record set 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:

• If you need to access the same tables multiple times, you greatly reduce access time, because the data is already in memory (in the record set).

  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 MX host computer.

• 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, if you need 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 employees' names. When users select an employee, the application displays the employee's details 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.

Performing a Query of Queries

There are four steps to perform a Query of Queries.

To perform a Query of Queries:

1. Generate a record set through a master query.

   You can write a master query using a tag or function that creates a record set. For more information, see Creating a record set.

2. Write a detail query--a cfquery tag that specifies dbtype="query".
3. 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.
4. 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. You must 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.

To use the results of a query in a query:

1. 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>
   

2. Save the page as query_of_query.cfm in the myapps directory under the web_root.
3. Display query_of_query.cfm in your browser

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:

cfset LastNameSearch="Doe"

<cfquery datasource="cfdocexamples"
name="master"
   cachedwithin=#CreateTimeSpan(0,1,0,0)#>
   SELECT *    from Employee
</cfquery>

<cfquery dbtype="query" name="detail">
   SELECT Emp_ID, FirstName, LastName
   FROM master
   WHERE LastName=<cfqueryparam
   value="#LastNameSearch#" 
   cfsqltype="cf_sql_char" 
   maxLength="20"></cfquery>

<cfoutput query=detail>
   #Emp_ID#: #FirstName# #LastName# <br>
</cfoutput>

<cfoutput>
   #master.columnlist#<br>
</cfoutput>

<cfoutput>
   #detail.columnlist#<br>
</cfoutput>

Displaying record set data incrementally

If your database is large, you can limit the number of rows displayed at one time. The following example shows how to use the currentRow query variable of a Query of Queries to do this. For more information on query variables, see Getting information about query results.

To display record set data incrementally:

1. 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>&nbsp;</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>
   

2. Save the page as qoq_next_row.cfm in the myapps directory under the web_root.
3. 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 CFML Reference.

Using Query of Queries with non-SQL record sets

A Query of Queries can operate on any CFML tag or function that returns a record set; you are not limited to operating on cfquery results. You can perform queries on non-SQL record sets, such as a cfdirectory tag, a cfsearch tag, a cfldap tag, and so on.

The following example shows how a Query of Queries interacts with the record set of a Verity search. This example assumes that you have a valid Verity collection, called bbb, which contains documents with a target word, film, or its variants (films, filmed, filming). Change the name of the collection and the search criteria to as appropriate for your Verity collection. For more information on Verity, see Building a Search Interface.

To use Query of Queries with a Verity record set:

1. Create a ColdFusion page with the following content:

   <html>
   <head>
   <title>QoQ and Verity</title>
   </head>
   
   <body>
   <!--- master query: retrieve all documents from the bbb collection 
   that contain 'film' (or its stemmed variants); change values for 
   collection and criteria as needed for your Verity collection --->
   <cfsearch name = "quick"
   	collection="bbb" 
   	type = "simple"
   	criteria="film">
   
   <h3>Master query dump:</h3>
   <cfdump var="#quick#">
   
   <!--- detail query: retrieve from the master query only those 
   documents with a score greater than a criterion (here, 
   0.7743) --->
   <cfquery name="qoq" dbtype="query">
   	SELECT * from quick
   	WHERE quick.score > 0.7743
   </cfquery>
   
   <h3>Detail query dump:</h3>
   <cfdump var="#qoq#">
   
   </body>
   </html>
   

2. Save the page as qoq_verity.cfm in the myapps directory under the web_root.
3. Display qoq_verity.cfm in your browser

The next example shows how a Query of Queries combines record sets from a cfdirectory tag, which is limited to retrieval of one file type per use.

To use Query of Queries to combine record sets:

1. Create a ColdFusion page with the following content:

   <html>
   <head>
   <title>Images Folder</title>
   </head>
   
   <body>
   <h2>Image Retrieval with QoQ</h2> 
   <!--- set the images directory --->
   <cfset dir = ("C:\pix\")>
   
   <!--- Retrieve all GIFs --->
   <cfdirectory name="GetGIF"
     action="list"
     directory="#dir#"
     filter="*.gif">
   
   <!--- Retrieve all JPGs --->
   <cfdirectory name="GetJPG"
     action="list"
     directory="#dir#"
     filter="*.jpg">
     
   <!--- Join the queries with a UNION in a QoQ (cfdirectory 
         automatically returns the directory name as "Name") --->
   <cfquery dbtype="query" name="GetBoth">
     SELECT * FROM GetGIF
     UNION 
     SELECT * FROM GetJPG
     ORDER BY Name
   </cfquery>
   
   <!--- Display output in a linked, ordered list --->
   <cfoutput>
     <p>The <strong>#dir#</strong> directory contains #GetBoth.RecordCount#
   
   images:<br>
     <ol>
       <cfloop query="GetBoth">
         <li><a href="../images/#Name#">#GetBoth.Name#</a><br>
       </cfloop>  
     </ol>
   </cfoutput>
   
   </body>
   </html>
   

2. Save the page as qoq_cfdirectory.cfm in the myapps directory under the web_root.
3. Display qoq_cfdirectory.cfm in your browser

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7

Comments

Add Comment

DannoParker said on Jul 11, 2005 at 11:55 AM :

I'm using cfqueryparam with Query of Queries and ran into a small problem. If the user were to search for the string "men's room" with the single quote, cfqueryparam escapes it with a second single quote (i.e. men''s room) and I get no results from the query of queries. If I pass the "raw" query string to the query of queries (without using cfqueryparam), the query of queries escapes the single quote for me automatically and I get the expected result. Should I forget about using cfqueryparam in the query of queries?

DannoParker said on Jul 13, 2005 at 5:53 AM :

Well, for those of you who might benefit from it, I came up with a workaround. I'm replacing instances of two single quotes with one single quote. This lets the single quotes "slip by." Hope this helps!

<cfqueryparam value="%#replace(lcase(url.query),"''","'",'all')#%" cfsqltype="CF_SQL_VARCHAR">

opalcom said on May 15, 2006 at 5:39 AM :

You should also be able to use PreserveSingleQuotes(), which is meant for exactly that purpose.

BlueSpline said on Oct 28, 2006 at 11:41 PM :

Work appears to be a reserved word in query of queries. You can create a query containing a column called work, but you can't then reference this column in a QoQ.

Mike

a440guy said on Jun 15, 2007 at 2:01 PM :

This page says that one can "perform joins and union operations on results from different data sources". It should also say that it CANNOT do outer joins of any Kind (neither left, right, nor full) when db_type="query".

gr8white said on Nov 7, 2007 at 11:41 AM :

QoQ seems to ignore leading and trailing spaces when matching strings.

name qTest
statement select 'TEST ' x
from dual


datasource aqrt_dev
record count 1
execution time 0ms


D:\development\aq\kensreport.cfm @ 13:25:27.215

name
statement select *
from qTest
where x = 'TEST '


datasource
record count 0
execution time 0ms


D:\development\aq\kensreport.cfm @ 13:25:27.215

name
statement select *
from qTest
where x = 'TEST'


datasource
record count 1
execution time 0ms


D:\development\aq\kensreport.cfm @ 13:25:27.230

name qTest
statement select ' TEST' x
from dual


datasource aqrt_dev
record count 1
execution time 15ms


D:\development\aq\kensreport.cfm @ 13:25:27.230

name
statement select *
from qTest
where x = ' TEST'


datasource
record count 0
execution time 0ms


D:\development\aq\kensreport.cfm @ 13:25:27.230

name
statement select *
from qTest
where x = 'TEST'


datasource
record count 1
execution time 0ms

John from NorthYorks said on Jan 10, 2008 at 1:07 AM :

There is a full list of reserved words (including 'work' ) at http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00001265.htm

To use a reserved word as a column name, enclose it in [square] brackets

halL said on Jan 10, 2008 at 1:49 PM :

The correct link for the reserved word information is Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00000187.htm.
This is a master page with several subtopics, including reserved words in queries.

Collectonian said on Aug 22, 2008 at 8:46 AM :

Note: you can not do a proper join while doing a query of a query (i.e. table1 INNER JOIN table2). You must use where joins instead:

SELECT column1, column2, column3
FROM table1, table2
WHERE table1.primarykeycol = table2.foreignkeycol

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00001265.htm

Add Comment