ColdFusion Query of Queries (Streamline your Processes)

In this article, you will learn:

1. What is a Query of Queries?
2. What are the benefits of using a Query of Queries?
3. How to conduct a Query of Queries.
4. What are some functionalities of ColdFusion Query of Queries?

Finding detailed information in a large database can sometimes be a daunting task.

Fortunately, with ColdFusion, you make your searches and data pull a breeze with Query of Queries! Utilizing this awesome function is sure to make any CIO or Project Leader’s job easier.

 

Let’s dive right in!

What is a Query of Queries?

A Query of Queries is a unique feature in its own respect because you can’t have one without… well, queries. You see, after you have created a recordset with a tag or function (such as cfquery), data can be recalled. It may be recalled individually or as part of a dependent query.

That recall is known as a Query of Queries. Any query that retrieves data from a recordset is a Query of Queries. It can also be known as a Memory Query because recordsets can be formed in other ways than the cfquery tag.

ColdFusion QoQ can used for other queries as well. For example, you can actually query a non-query database object as well.
It’s a super handy feature that has many benefits.

ColdFusion Query of Queries Benefits

Let’s take a look at a few of the benefits of ColdFusion QoQ.

Convenient Table Recall

There are times when you need recall the same tables over and over again. And the bigger the table- the more frustrating it can get. But ColdFusion QoQ makes things easier because the data is already in the recordset. QoQ is perfect for tables between 5000 and 50000 rows. Its only limitation is the memory of the ColdFusion host computer.

Join and Union Operations

Using QoQ allows you to perform join and union operations on results from completely different databases. This can definitely come in handy. For instance, you can use a union operation on queries from separate databases in order to eliminate duplicates from an email list of clients.

Manipulation of Cached Query Results Made Easy

This is particularly useful for CIOs and Team Leaders. After performing a query on a database just once, your results can be used to generate many different tables. This comes in handy for creating summary tables for employee salaries, infographics, or availabilities. Just make a single query to your employee database and use the results given to generate three separate tables from just one pull.

Caching Database Results

Wouldn’t it be nice to have drill-down details on things without having to access a database? You can by using QoQ. For instance, you can pull a query on employee information using QoQ and then cache it. Afterwards, create a table or drop-down list of employee names.

This way when users select a particular employee name, your application accesses the cached data vice needing to call the database.

Generating Subreport Data

Subreports are an awesome way to nest a report inside your other reports. This can become a very convenient way to post secondary information within your primary presentation. Subreports are great if you wish to avoid using complex SQL or if your report requires data from multiple data sources. QoQ helps you to generate these subreports and streamline your reports.

How to Successfully Conduct a ColdFusion Query of Queries

When it comes to conducting a ColdFusion QoQ, it’s a pretty simple process. Just follow these four easy steps:

1. Create a recordset through a master query. Master queries may be generated through tags or functions that create recordsets.
2. Compose a detail query. This would be a cfquery tag that specifies dbtype= “query”
3. Write an SQL statement that retrieves the applicable information. A datasource does not need to be specified at this point. However, specify the names of one or more currently existing queries as table names within your SQL code.
4. Your database content should be changing rapidly. But if it doesn’t, manipulate the cachedwithin attribute of your master query to cache queries between page requests. Use the CreateTimeSpan function (in days, hours, minutes, seconds format) to specify the value. Doing so allows for your ColdFusion to access the database on the first request. The database will only be queried again after the set time has expired.

Adobe provides an excellent example for creating and using these results.

<h1>Employee List</h1>

<!— LastNameSearch (normally generated interactively) —>

<cfset LastNameSearch=”Doe”>

<!— Master Query —>

<cfquery datasource=”cfdocexamples” name=”master”

cachedwithin=#CreateTimeSpan(0,1,0,0)#>

SELECT * from Employee

</cfquery>

<!— Detail Query (dbtype=query, no data source) —>

<cfquery dbtype=”query” name=”detail”>

SELECT Emp_ID, FirstName, LastName

FROM master

WHERE LastName=<cfqueryparam value=”#LastNameSearch#”

cfsqltype=”cf_sql_char” maxLength=”20″></cfquery>

<!— output the detail query results —>

<p>Output using a query of query:</p>

<cfoutput query=detail>

#Emp_ID#: #FirstName# #LastName#<br>

</cfoutput>

<p>Columns in the master query:</p>

<cfoutput>

#master.columnlist#<br>

</cfoutput>

<p>Columns in the detail query:</p>

<cfoutput>

#detail.columnlist#<br>

</cfoutput>

Querying a Non-Database Query Object

One of the best things about QoQ is that you can query a non-database query object. An example of this is retrieving the results of a <cfftp> directory listing.

Functionalities of ColdFusion Query of Queries

ColdFusion QoQ has many functionalities and their relation to QoQ is as follows:

• Dot Notations

  • QoQ supports using Dot Notation in table names.

• Joins

  • QoQ supports joins between two tables.

• Unions

  • A UNION operator allows you to combine the results of two or more SELECT expressions into a single recordset.

• Conditional Operators

  • QoQ allows you to take advantages of many different conditional operators including:
    • Test Conditional
    • Null Conditional
    • Comparison Conditional
    • BETWEEN Conditional
    • IN Conditional
    • LIKE Conditional

• Managing Data Types for Columns

  • QoQ requires that every column must have defining metadata. Queries created with the QueryNew function that omits the second parameter does not contain metadata. Use the optional second parameter to define data types.

• Using the CAST Function

  • Use QoQ to convert a column value into a correct expression of the correct data type.

• Using Aggregate Functions

  • Use these functions for retrieving summary information from a table instead of retrieving the entire table and then operating on its recordset.

• Using group by and having expressions

  • QoQ supports the use of any arithmetic expression as long as it is referenced via alias.

• Using ORDER BY clauses

  • QoQ supports ORDER BY clauses as long as it is the last clause in your SELECT statement.

• Using Aliases

  • QoQ supports the use of database column aliases. You can also reuse an alias in the same SQL statement.

• Handling Null Values

  • QoQ uses Boolean logic to handle conditional expressions. Proper handling of NULL values requires the use of ternary logic.

• Concatenating Strings

  • QoQ supports two string concatenation operators: + and ||

• Escaping Reserved Keywords

  • QoQ supports a large arrangement of reserved keywords, but does not support nested escapes.

• Using QoQ with Dates

  • If you create a query object with the QueryNew function and populate a column with date constants, ColdFusion stores the dates as a string inside the query object until a Query of Queries is applied to the query object. When ColdFusion applies a Query of Queries to the query object, it converts the string representations into date objects.

ACF vs Lucee QoQ

ACF and Lucee QoQ are very similar, but there are several differences due to the SQL engines used in each CFML engine. These differences present themselves more so in difficult SQL statements–such as grouping functions. A simple rewrite of the SQL code should absolve most complications with Lucee QoQ.

Summary?

ColdFusion’s Query of Queries is a wonderful tool that makes your CF lives a whole heck of a lot more convenient. It streamlines your processes and creates elegant output that you and your company can easily use.

When have you found query of queries to be most helpful? What practical applications do you use it for?

Let us know in the comments below. And… If what you like what you read, you can always follow us on Twitter @CFTeraTech and make sure you don’t miss an update.

Cheers!

And to continue learning how to make your ColdFusion apps more modern and alive, I encourage you to download our free ColdFusion Alive Best Practices Checklist.

Because… perhaps you are responsible for a mission-critical or revenue-generating CF application that you don’t trust 100%, where implementing new features is a painful ad-hoc process with slow turnaround even for simple requests.

What if you have no contingency plan for a sudden developer departure or a server outage? Perhaps every time a new freelancer works on your site, something breaks. Or your application availability, security, and reliability are poor.

And if you are depending on ColdFusion for your job, then you can’t afford to let your CF development methods die on the vine.

You’re making a high-stakes bet that everything is going to be OK using the same old app creation ways in that one language — forever.

All it would take is for your fellow CF developer to quit or for your CIO to decide to leave the (falsely) perceived sinking ship of CFML and you could lose everything—your project, your hard-won CF skills, and possibly even your job.

Luckily, there are a number of simple, logical steps you can take now to protect yourself from these obvious risks.

No Brainer ColdFusion Best Practices to Ensure You Thrive No Matter What Happens Next

ColdFusion Alive Best Practices Checklist

Modern ColdFusion development best practices that reduce stress, inefficiency, project lifecycle costs while simultaneously increasing project velocity and innovation.

√ Easily create a consistent server architecture across development, testing, and production

√ A modern test environment to prevent bugs from spreading

√ Automated continuous integration tools that work well with CF

√ A portable development environment baked into your codebase… for free!

 

Learn about these and many more strategies in our free ColdFusion Alive Best Practices Checklist.