After you have created a record set with a tag or function, you can query the record set in several 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.
Performing a Query of Queries has many benefits, including the following:
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 Server host machine.
For example, you can perform a union operation on queries from different databases to eliminate duplicates for a mailing list.
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.
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.
There are four steps to perform a Query of Queries.
You can write a normal query using a tag or function that creates a record set. This is sometimes called a master query. For more information, see "Creating a record set".
dbtype=
"query"
in its cfquery
tag.
datasource
attribute.
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 results 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.
<body> <h1>Employee List</h1> <!--- LastNameSearch (normally generated interactively) ---> <cfset LastNameSearch="Doe"> <!--- Master Query ---> <cfquery datasource="CompanyInfo" 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> <br> <p>Columns in the master query:</p> <cfoutput> #master.columnlist#<br> </cfoutput> <br> <p>Columns in the detail query:</p> <cfoutput> #detail.columnlist#<br> </cfoutput> </body>
http://127.0.0.1/myapps/query_of_query.cfm
The following figure shows how the output appears:
The master query retrieves the entire Employee table from the CompanyInfo data source (the CompanyInfo database). 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:
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".
<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 = "CompanyInfo"> 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">#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>
http://127.0.0.1/myapps/qoq_next_row.cfm
The following figure shows how the output appears:
As you debug your CFML code, you can use the cfdump
tag to quickly present the value of your query. This tag has the following format:
<cfdump var="#query_name#">
For more information on the cfdump
tag, see CFML Reference.
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, Verity searches, 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 Chapter 24, "Building a Search Interface".
<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>
http://127.0.0.1/myapps/qoq_verity.cfm
The following figure shows how the output appears:
Note: This figure shows a collapsed master query output and an expanded detail query output. Click an output to expand or collapse it.
The first cfdump
tag shows the master query, which retrieves all records. The second cfdump
shows the Query of Queries results.
Tip: Adjust the score criterion of the detail query to reflect the contents of your collection.
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.
<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>
http://127.0.0.1/myapps/qoq_cfdirectory.cfm
The following figure shows how the output appears: