ColdFusion Query of Queries (QoQ)
ColdFusion Query of Queries (QoQ) let you query the results of an existing database query. You can also query a non-database query object, for example, the results of a cfftp directory listing.
Syntax
To create a Query of Queries, you use the cfquery tag (just like with a database query), and specify dbtype="query". Then, within the SQL, your from keyword needs to indicate the name of the query that you are querying. So, in other words, instead of querying a table, you are querying a query.
<cfquery dbtype="query" name="NameOfQoQ">
select *
from NameOfAnotherQuery
where ColumnName = 'SomeValue'
</cfquery>
Query an Existing Database Query
In this example, we peform a query against a database, retrieving all records from the Individual table. After outputting the results, we then create a Query of Queries, which queries the resultset of the first query. We also output the results of this query.
<cfquery datasource="quackit" name="GetAllUsers">
select *
from Individual
</cfquery>
<b>All Users (results of initial query)</b><br />
<cfoutput query="GetAllUsers">
#FirstName# #LastName#<br />
</cfoutput>
<br />
<cfquery dbtype="query" name="GetSomeUsers">
select FirstName, LastName
from GetAllUsers
where FirstName = 'Homer'
</cfquery>
<b>Users with "Homer" as their first name (results of QoQ)</b><br />
<cfoutput query="GetSomeUsers">
#FirstName# #LastName#<br />
Depending on the contents of the database and the FTP site, the above code could result in something like this:
Fred Flinstone
Homer Simpson
Homer Brown
Ozzy Ozzbourne
Homer Gain
Results of QoQ
Homer Simpson
Homer Brown
Homer Gain
Query a Non-Database Query Object
One of the great things about QoQ is that you can query a non-database query object. An example of a non-database query object is the results of a cfftp directory listing. This is where you use the cfftp tag to list the contents of a directory on a remote server.
The following example uses the cfftp tag to list the contents of a remote server. It then uses a QoQ to filter out only those files which are greater than 1024 bytes in length.
<cfftp
action="listDir"
name="RemoteDirectoryContents"
directory=""
server="localhost"
username="homer"
password="simpson">
<b>All Files (initial query object)</b><br />
<cfoutput query="RemoteDirectoryContents">
#URL# (#length# bytes)<br />
</cfoutput>
<br />
<cfquery dbtype="query" name="GetSomeContents">
select URL, length
from RemoteDirectoryContents
where length > 1024
</cfquery>
<b>Files bigger than 1024 bytes (results of QoQ)</b><br />
<cfoutput query="GetSomeContents">
#URL# (#length# bytes)<br />
</cfoutput>
Depending on the contents of the directory, the results might look something like this:
ftp://localhost/AboutUs.html (98345 bytes)
ftp://localhost/images (0 bytes)
ftp://localhost/index.html (4 bytes)
ftp://localhost/ReadmeNow.htm (6500 bytes)
Results of QoQ
ftp://localhost/AboutUs.html (98345 bytes)
ftp://localhost/ReadmeNow.htm (6500 bytes)
