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.

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?

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. 1. 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.

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.

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.

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.

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>

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

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

SELECT * from Employee

SELECT Emp_ID, FirstName, LastName

FROM master

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

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

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

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

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

#master.columnlist#<br>

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

#detail.columnlist#<br>

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:

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.

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.

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.