ColdFusion Query of Queries (QoQ)

ColdFusion Query of Queries (QoQ) lets 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.

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.

Depending on the contents of the database and the FTP site, the above code could result in something like this:

Result
All Users (results of initial query)
Fred Flinstone
Homer Simpson
Homer Brown
Ozzy Ozzbourne
Homer Gain

Users with "Homer" as their first name (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.

Depending on the contents of the directory, the results might look something like this:

Result
All Files (initial query object)
ftp://localhost/AboutUs.html (98345 bytes)
ftp://localhost/images (0 bytes)
ftp://localhost/index.html (4 bytes)
ftp://localhost/ReadmeNow.htm (6500 bytes)

Files bigger than 1024 bytes (results of QoQ)
ftp://localhost/AboutUs.html (98345 bytes)
ftp://localhost/ReadmeNow.htm (6500 bytes)