ColdFusion Query of Queries (Streamline your Processes)

In this article, you will learn:

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

Finding detailed information in an extensive 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 excellent function is sure to make any CIO or Project Leader’s job easier.

 

Let’s dive right in!

What is a ColdFusion Query of Queries?

A Query of Queries is a unique feature in its 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 also be used for other queries. For example, you can query a non-query database object.
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 to recall the same tables repeatedly, and the bigger the table, the more frustrating it can be. However, ColdFusion QoQ makes things easier because the data is already in the record set. 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 entirely 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 do this by using QoQ. For instance, you can pull a query on employee information using QoQ and then cache it. Afterward, 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 without needing to call the database.

Generating Subreport Data

Subreports are an excellent 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

Conducting a ColdFusion QoQ is 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 detailed query. This would be a cfquery tag that specifies dbtype= “query”
3. Write an SQL statement that retrieves the applicable information. A data source does not need to be specified at this point. However, select 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, and seconds format) to specify the value. Doing so allows 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 of how to create and use 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 / CFQuery

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 advantage 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 omit the second parameter do 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 proper data type.

• Using Aggregate Functions

  • Use these functions ftorretrievesummary 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. The 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

  • Suppose you create a query object with the QueryNew function and populate a column with date constants. In that case, 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 are more apparent in complex SQL statements, such as grouping functions. A simple rewrite of the SQL code should solve most of the complications with Lucee QoQ.

Summary?

ColdFusion Query of Queries is an excellent tool that makes your CF life a whole 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. Iff you like what you read, you can always follow us on Twitter @CFTeraTech to ensure 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.