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.
Use Query of Queries with a Verity record set
- 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>
- Save the page as qoq_verity.cfm in the myapps directory under the web_root.
- 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.
Use Query of Queries to combine record sets
- 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>
- Save the page as qoq_cfdirectory.cfm in the myapps directory under the web_root.
- Display qoq_cfdirectory.cfm in your browser
