Query of Queries (CFSQL)

One of the new features introduced with the release of ColdFusion 5 was the query of queries. The query of queries (CFSQL) is actually an extension of the CFQUERY tag that we have already covered. With a basic CFSQL, you begin with a CFQUERY tag just as we have done in previous examples. You then write a second CFQUERY tag with two modifications you do not use the datasource attribute and you set the DBTYPE attribute to query.

There are a number of advantages offered by the use of a CFSQL. The first potential use is to reduce the interaction with the database.

In Listing 24.16, we are querying our Product table for information about all products that are in category 21. The result set then is cached in memory for one hour. We now can run queries about individual products or groups of products without having to make additional calls to our database. We simply run a query against the in-memory recordset.

Listing 24.16 Query of a Query

 <cfquery name="ProductList" datasource="#Request.DSN#"       cachedWithin=#CreateTimeSpan(0,1,0,0)#>            SELECT ProductID, ProductName, ProductDescription,            ProductPricePerUnit            FROM Product            WHERE CategoryID = 10  </cfquery>  <cfquery CFQUERY name="GetProduct" dbtype="query" >       SELECT *       FROM ProductList       WHERE ProductID = #Form.ProductID#  </cfquery> 

Although it is possible to store large recordsets in memory, we are again in a situation of "just because we can" does not mean that we should. Placing a large number of record sets in memory in this manner makes sense only when a high number of the records are accessed repeatedly or the recordset itself is repeatedly called, such as in a sorting scenario.

Another use of the CFSQL is to create joins or unions across datasources. In Listing 24.17, we have created a union of the recordsets of two queries with different data sources. This is particularly useful if you need to interact with legacy applications. In Listing 24.18, we demonstrate the same principle, but with a join.

Listing 24.17 Creating a Union with a CFSQL

 <cfquery name="SurfProductList" datasource="MyTest">       SELECT ProductID, ProductName, ProductDescription,       ProductPricePerUnit       FROM Product       WHERE CategoryID = 10  </cfquery>  <cfquery name="SkiProductList" datasource       ="MyTest2">       SELECT ProductID, ProductName, ProductDescription,       ProductPricePerUnit       FROM Product       WHERE CategoryID = 11  </cfquery> <cfquery name="GetProduct" DBTYPE="query">       SELECT ProductID, ProductName, ProductDescription,       ProductPricePerUnit       FROM SurfProductList       UNION       SELECT ProductID, ProductName, ProductDescription,       ProductPricePerUnit       FROM SkiProductList  </cfquery> 

Listing 24.18 Creating a Join with a CFSQL

 <cfquery name="NewCustomerList" datasource="MyTest">       SELECT CustomerID, CustomerFName, CustomerLName, CustomerAddress,       CustomerCity, CustomerState, CustomerZipCode       FROM Customer  </cfquery>  <cfquery name="CustomerCCList" datasource       ="MyTest2">       SELECT CustomerID, CustomerCCNumber, CustomerCCType,       CustomerCCExpire       FROM CustomerCC  </cfquery>  <cfquery name="GetCustomers" DBTYPE="query">       SELECT *       FROM NewCustomerList,CustomerCCList       WHERE  NewCustomerList.CustomerID = CustomerCCList.CustomerID  </cfquery> 

There are a number of ColdFusion tags, such as CFDIRECTORY, CFFTP, CFPOP, CFSEARCH, CFREGISTRY,CFHTTP, and CFPROCRESULTS, that return query objects. In addition, many CFX and custom tags return query objects that also can be used with CFSQLs. The CFSQLs can be used to query on the result sets or create a join of the result sets of these query objects.

In Listing 24.19, we are using the CFPOP tag to obtain the header information about the first 25 emails on a POP mail server. The result set is returned as a query object, which the code queries, looking for only those emails that are from the well-known ColdFusion mail list, CFGURUS.

Listing 24.19 Using a CFSQL to Query a Query Object

 <cfpop server="www.mypoperver.com" username="myaccount" password="topsecret"  actions="getheaderonly" name="testpop">  <cfquery name="getmail" dbtype="query">       SELECT testpop.From as Who ,testpop.Subject as What       FROM testpop       WHERE testpop.Subject LIKE '%cfgurus%'  </cfquery>  <cfoutput>  Mail Count for Testpop is #testpop.RecordCount#  </cfoutput><BR>  <B>CFDJLIST</B><BR>  <cfoutput QUERY="getmail">  Row: #CurrentRow#  From: #Who#  Subject:#.What# <BR>  </cfoutput> 

Keep in mind that any ColdFusion tag that returns a query object can be used with a CFSQL. We could have just as easily used a second CFPOP tag to obtain emails from work and then created a union of the two with the CFSQL. We can also use CFSQLs to join database and nondatabase result sets.

As you can see, using the CFSQL is a simple concept that has a lot of potential uses. The CFSQL enables you to query result sets in memory, create cross-data source joins, query nondatabase sources, and join database and nondatabase queries. There are, however, some limitations that we must discuss. The first is that you can perform only SELECTS; keep in mind that you are performing your queries against an existing result set, not the database. CFSQL also does not support comparisons of open database connectivity ODBC dates or nested aggregate functions.