This chapter is from the book
This chapter is from the book
This chapter is from the book
Query of Queries
ColdFusion 5.0 has brought a new and very useful feature to the query world. "Query of Queries" has turned over a new leaf in how we access and manipulate data from any source. It allows developers to "re-query" an existing dataset already compiled from a procedure call.
An important feature is the ability to access data from two datasources and combine their results. For example, you could use this feature to combine customer data from an Oracle data warehouse and an SQL Server customer file through the use of a UNION statement. Instead of creating a structure (or any complex data type) to hold all the data, you can now natively use a recordset for your display.
This feature also works with any procedure that returns a query result set. These include: CFPOP, CFLDAP, CFHTTP, CFINDEX, CFSTOREDPROC, or any of the functions that would return a query.
Improved performance will be apparent because you don't have to make a database call many times. Once created, the data remains in memory and will take less of a toll on the system to produce a result set. You can manipulate this data in a much cleaner and quicker way than with traditional query methods.
We'll take the previous example and enhance it using the query of queries feature. First, let's inspect what you will have to do to pull off this new feature.
Script 1-2 firstQueryofQ.cfm
<CFQUERY NAME="getCustomers" DATASOURCE="shelleyCatalog"> SELECT * FROM Customers </CFQUERY> <CFQUERY NAME="QofQ" DBTYPE="QUERY"> SELECT * FROM getCustomers WHERE FirstName LIKE '%Mike%' </CFQUERY> <CFOUTPUT QUERY="QofQ"> <TABLE> <TR> <TD>#customerID#</TD> <TD>#firstName#</TD> <TD>#lastName#</TD> <TD>#address#</TD> <TD>#city#</TD> <TD>#state#</TD> <TD>#zip#</TD> <TD>#country#</TD> <TD>#phone#</TD> <TD>#email#</TD> </TR> </TABLE> </CFOUTPUT>
How this works
Create the query getCustomers as you did in the previous example (script 1-1).
Now create a new query called QofQ, which will SELECT all ( * ) from the query getCustomers. This queries the original query.
Output the results.