This technique radically boosts application efficiency by significantly reducing external database load, decreasing network latency, and making complex data manipulation such as joining data from different databases or creating summary reports incredibly fast. Whether you are manipulating cached results, performing convenient table recall, or simply seeking better performance, mastering QoQ is essential for robust ColdFusion development. If your team needs expertise to fully leverage QoQ and other advanced features, consider our dedicated ColdFusion Development Services or specialized ColdFusion Consulting to optimize your processes and achieve maximum scalability.
- What is a ColdFusion Query of Queries?
- What are the benefits of using a ColdFusion Query of Queries?
- How do you conduct a ColdFusion query of queries?
- What are some functionalities of ColdFusion Query of Queries?
Contents
Searching for specific information in a large database can feel slow and frustrating.
ColdFusion helps you make data pulls faster and easier with Query of Queries—a practical feature that can simplify reporting and improve performance without repeated trips to the database. Used well, QoQ can make life easier for CIOs, project leaders, and development teams alike.
Let’s jump in.
ColdFusion Query of Queries Essentials
• Definition: Run SQL against in-memory query results (recordsets).
• Key Benefits: Reduce DB load, enable cross-database joins/unions, and reuse cached data for drill-down reporting.
• How-To: 1) Run a master <cfquery>. 2) Run a detail <cfquery dbtype=”query”> that references the master query.
• Limits: Read-only (no DML); memory-bound; join support via WHERE syntax only (no INNER/OUTER join keywords).
• QoQ Features: Aggregates, GROUP BY, ORDER BY, CAST, LIKE, and more ( docs.lucee.org).
What is a ColdFusion Query of Queries?
A Query of Queries is unique because it depends on queries you already ran. After you create a recordset using a tag or function (like cfquery), you can recall and re-query that data—either on its own or as part of another query.
That in-memory recall is what’s known as a Query of Queries. Put simply: any query that retrieves data from an existing recordset is a QoQ. You may also hear it called a Memory Query, because recordsets can be created in ways other than the cfquery tag.
ColdFusion QoQ can also be applied beyond traditional database queries—for example, you can query a non-query database object.
It’s a highly practical feature with real performance and workflow benefits.
ColdFusion Query of Queries Benefits
Here are several ways ColdFusion QoQ can help you streamline development and improve performance.
Convenient Table Recall
If you need to reuse the same tables repeatedly, repeatedly querying large datasets can become a bottleneck. With QoQ, your data is already in the recordset, so re-filtering and reshaping results is faster. QoQ is often a good fit for tables in the 5,000 to 50,000 row range, with the primary limitation being the available memory on the ColdFusion host.
Join and Union Operations
QoQ lets you perform join and union operations on results pulled from completely different databases—an extremely useful capability in real-world applications. For example, you can run a UNION across queries from separate databases to help remove duplicates from a client email list.
Manipulation of Cached Query Results Made Easy
This is especially valuable for CIOs and team leaders who want performance without complexity. You can query the database once and reuse those results to generate multiple views and reports—such as employee salary summaries, infographic datasets, or availability tables. Instead of multiple database pulls, you do one, then derive multiple outputs.
Caching Database Results
Want drill-down details without hammering the database? QoQ supports that pattern. For instance, you can query employee information once, cache it, and then build a table or dropdown list of employee names.
When users select an employee, your application can read from cached results rather than hitting the database again.
Generating Subreport Data
Subreports are a clean way to nest a report inside another report, presenting secondary information alongside primary results. They’re especially helpful when you want to avoid overly complex SQL or when your report needs multiple data sources. QoQ can generate subreport datasets efficiently to help streamline reporting.
How to Successfully Conduct a ColdFusion Query of Queries
Running a ColdFusion QoQ is straightforward. Follow these four steps:
1. Create a recordset via a master query. Master queries can come from tags or functions that generate recordsets.
2. Create a detailed query using a cfquery tag with dbtype=”query”.
3. Write the SQL that retrieves the data you need. You don’t specify a data source here. Instead, reference one or more existing queries as table names in your SQL.
4. If your underlying database content changes slowly, consider caching: use the cachedwithin attribute on the master query to cache results between page requests. Use CreateTimeSpan (days, hours, minutes, seconds) to define the cache duration. ColdFusion queries the database on the first request and refreshes only after the cache expires.
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 / 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
- QoQ allows you to take advantage of many different conditional operators , including:
-
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.
FAQs About ColdFusion Query of Queries
What is the main purpose of using ColdFusion Query of Queries (QoQ)?
The primary purpose of a ColdFusion Query of Queries is to manipulate, filter, and sort data retrieved from a database after the initial data pull, without making additional trips to the original database server. This is an essential technique for caching database results and improving application performance, especially when performing multiple operations on a single dataset. According to the Query of Queries user guide on the Adobe Help Center, QoQ allows developers to re-query an existing record set already held in memory on the ColdFusion server.
What are the key benefits of using QoQ for data manipulation?
QoQ offers several performance and flexibility benefits:
- Reduced database load: It minimizes database round trips, as subsequent queries execute against the in-memory query object rather than the database. This is a crucial element of Performance Optimization and Server Tuning which is one of our core services.
- Cross-Database Operations: You can perform
JOINandUNIONoperations on results pulled from entirely different databases, which is not possible with standard SQL. - Simplified Reporting: It makes generating complex reports easy by allowing you to create multiple summary tables or subreports from a single, initial master query.
Does ColdFusion Query of Queries support standard SQL joins?
ColdFusion Query of Queries only supports joins through the WHERE clause syntax.
It is important to note that the QoQ engine does not support standard SQL joins using INNER JOIN or OUTER JOIN keywords. If you need full outer join functionality, you must perform that join in the original database query before it is stored as a ColdFusion query object.
How do I implement a ColdFusion Query of Queries?
A Query of Queries is implemented by following two main steps:
- Master Query: Execute a standard
<cfquery>with adatasourceattribute to fetch the data from the database into a named query object (e.g.,name="masterQuery"). - Detail/QoQ Query: Execute a second
<cfquery>using the attributedbtype="query"and reference the name of your master query in theFROMclause.
Example Code:
<cfquery name="allUsers" datasource="cfdocexamples"> SELECT UserID, Name, Status FROM Users </cfquery> <cfquery name="activeUsers" dbtype="query"> SELECT UserID, Name FROM allUsers WHERE Status = 'Active' </cfquery>
Can QoQ be used with modern ColdFusion Component (CFC) development?
Yes, ColdFusion Query of Queries can be used in modern script-based development, but the syntax requires you to explicitly pass the master query object. When using the script-based component, such as ColdFusion 9's Query.cfc, the query object must be passed as a named argument to the QoQ instance, because the QoQ execution context is separate from the calling page's Variables scope.
Example using Query.cfc (Script Syntax):
<cfset detailQuery = new Query( sql="SELECT Name FROM masterQuery WHERE City='London'", dbtype="query", masterQuery=masterQuery )/> <cfset results = detailQuery.execute().getResult()/>
Is Query of Queries a read-only operation?
Yes, Query of Queries is a read-only operation. It is designed for manipulating existing data and cannot be used to execute data modification language (DML) statements such as INSERT, UPDATE, or DELETE on the underlying database tables. For these operations, a regular <cfquery> with a standard datasource is required. If you require assistance with ensuring your data is handled securely and efficiently, explore our ColdFusion Consulting Experts & Agency Services.
