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:

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:

• << Previous
• Next Lesson >>