Querying Queries

The result of using <cfquery> is effectively a record set sitting in memory on the application server. This query might be available only for the current page execution, or it might be placed into a more permanent memory scope, such as the application, session, or server scope. It could even be a temporarily cached query, by way of the cachedwithin and cachedafter attributes. Although a query's information might be readily available to the application server, developers typically go back to the database if they need to manipulate the query data in some way (for example, to re-sort or filter the record set).

Shared variable scopes are covered in Chapter 10, "Application and Server Variables," and Chapter 11, "Session State Management."

Query caching is discussed in Chapter 48, "Improving Performance."

ColdFusion allows developers to reuse existing queries by running queries against them in memory. This gives you the advantage of being able to avoid the often costly performance hit of going back to the database to manipulate data that the application server has already recently called. A standard <cfquery> statement can be used to call a query variable in memory as though it were simply a table of data in an available data source. Query variables can even be joined, which provides interesting possibilities for joining data from disparate information sources.

Manipulating Existing Queries

You query existing queries by using <cfquery> with the dbtype="query" attribute set (and no datasource attribute specified). You form the query SQL in exactly the same way you would expect to interrogate a data source, except that the data source table name is replaced with the name of the query:

 <!--- load the entire Employee table into memory ---> <cfquery name="users"          datasource="dsn">  SELECT *  FROM Users </cfquery> <!--- grab a subset of the data from users ---> <cfquery name="managers"          dbtype="query">  SELECT *  FROM Users  WHERE manager = 1 </cfquery> 

The query can be placed in any scope available to the page that is calling the <cfquery> tag.

Although the feature is powerful, it supports a subset of the available SELECT syntax. The usable SQL functions are:

• FROM

• WHERE

• GROUP BY

• UNION

• ORDER BY

• HAVING

• AS

• DISTINCT

Boolean Predicates

• LIKE

• NOT LIKE

• IN

• NOT IN

• BETWEEN

• NOT BETWEEN

• AND

• OR

• IS

• IS NOT

• IS NUL

Aggregate Functions

• Count([DISTINCT][*] expr)

• Sum([DISTINCT] expr)

• Avg([DISTINCT] expr)

• Max(expr)

• Min(expr)

Comparison Operators

• <=

• >=

• =

• <

• >

• <>

Conversion Functions

• Cast()

• Lower()

• Upper()

Concatenation

• +

• ||

The query of queries feature allows you to reorder the record set in memory without going back to the database. The column name that is being sorted by must be specified in the SELECT statement, as well as in the ORDER BY statement. This is slightly different from normal SQL code. For example, this works:

 SELECT EmpID, StartDate FROM Users ORDER BY StartDate 

but this fails:

 SELECT EmpID FROM Users ORDER BY StartDate 

Query of queries can generate computed columns (for example, by concatenating strings or through the use of aggregate functions):

 <!--- count the number of employees in each department ---> <cfquery name="DeptUsers"          dbtype="query">  SELECT COUNT(DeptID) AS emp_count, DeptID  FROM Users  GROUP BY DeptID </cfquery> 

GROUP BY and aggregate functions are discussed in Chapter 46, "Aggregates."

TIP

When you're using aggregate functions, it's good practice to alias the computed column name to something intuitive. For example, in the DeptUsers query, if the alias emp_count were not specified, the variable name would become COMPUTED_COLUMN_1.

NOTE

ColdFusion variables are supposed to be typeless; however, SQL is not. Use the Cast() function to cast ColdFusion variables to specific data types if needed.

NOTE

When you are performing string comparisons, query of queries is case sensitive. For example, the result returned from this:

 WHERE firstname LIKE 'G%' 

will be different from the result returned from this:

 WHERE firstname LIKE 'g%' 

Joining Different Data Sources

Query of queries can perform a single join of two query variables in memory by using standard SQL. ColdFusion is unconcerned with the origin of the data, and thus any two record sets can be joinedeven those from completely different information resources. This allows you to, for example, join tables or views from two different databases, LDAP or POP email queries, and so on.

POP email is discussed in Chapter 41, "Email Integration."

LDAP queries are covered in Chapter 42, "LDAP."

Information about table joins can be found in Chapter 45, "Joins."

An application might need to combine the customer information in a relational database with email received in a POP account after an online marketing campaign. Both the database and the POP server could be queried, and the individual result sets could be combined into a unified query variable for reporting:

 <!--- get emails from campaign account ---> <cfpop action="GETHEADERONLY"        name="GetMail"        server="mail.forta.com"        username="username"        password="password"> <!--- get customer records from database ---> <cfquery name="users"          datasource="dsn">  SELECT *  FROM Users </cfquery> <!--- join record sets on email address ---> <cfquery name="Responses"          dbtype="query"> SELECT  Users.Email AS Email,  Users.FirstName AS FirstName,  Users.LastName AS LastName,  GetMail.Subject AS Subject,  GetMail.Date AS Date FROM Users, GetMail WHERE Users.email = GetMail.from </cfquery> 

Any ColdFusion query can be used in query-of-queries processing. This includes the following:

• <cfcollection>

• <cfdirectory>

• <cfftp>

• <cfhttp>

• <cfindex>

• <cfldap>

• <cfpop>

• <cfprocresult>

• <cfquery>

• <cfsearch>

• <cfstoredproc>

• <cfwddx>

• Queries created using QueryNew()