TeraTech

The ColdFusion Experts: Develop | Secure | Optimize

  • Services
  • About
  • CF Alive
  • Blog
  • Podcast
  • Contact

  • Services
  • About
  • CF Alive
  • Blog
  • Podcast
  • Contact

ColdFusion Query of Queries (Streamline your Processes)

June 19, 2019 By Michaela Light Leave a Comment

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?

Contents

  • What is a Query of Queries?
  • ColdFusion Query of Queries Benefits
  • How to Successfully Conduct a ColdFusion Query of Queries
  • Functionalities of ColdFusion Query of Queries
  • ACF vs Lucee QoQ
  • Summary?

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

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

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

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

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

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.

Related Posts

  • ColdFusion Server Crashing –  First 3 Steps to Fix it FastColdFusion Server Crashing – First 3 Steps to Fix it Fast
  • ColdFusion Hosting Independent Review (How To Choose the Best One)ColdFusion Hosting Independent Review (How To Choose the Best One)
  • CFML Open Source: Everything You Need To Know About LuceeCFML Open Source: Everything You Need To Know About Lucee
  • 4 Ways to Recognize a Good ColdFusion Consultant4 Ways to Recognize a Good ColdFusion Consultant
  • Introducing Swansea Jack (Lucee CFML 6 announced)Introducing Swansea Jack (Lucee CFML 6 announced)
  • 096 Adobe ColdFusion 2020 Roadmap (Multi-cloud, micro-services and more), with Ashish Garg096 Adobe ColdFusion 2020 Roadmap (Multi-cloud, micro-services and more), with Ashish Garg
  • Facebook
  • Twitter
  • LinkedIn

Filed Under: cfquery, ColdFusion 2018, Database, Lucee, Query of Queries Tagged With: cfquery, ColdFusion, Programming, Query of Queries

← Previous Post 5 Great Conference Surviving Tips for Introverted Developers
Next Post → 092 How to start and run a CFUG (experts panel) with Daniel Garcia, Leon O’Daniel, Michaela Light and Nolan Erck- Transcript

Popular podcast episodes

  • Revealing ColdFusion 2021 – Rakshith Naresh
  • CF and Angular – Nolan Erck
  • Migrating legacy CFML – Nolan Erck
  • Adobe API manager – Brian Sappey
  • Improve your CFML code – Kai Koenig

CF Alive Best Practices Checklist

 

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

Top articles

  • CF Hosting (independent guide)
  • What is Adobe ColdFusion
  • Is Lucee CFML now better than ACF?
  • Is CF dead?
  • Learn CF (comprehensive list of resources)

Recent Posts

  • Funny ColdFusion Custom Tag Competition (CFML Programmer’s Jokes)
  • Protected: State of the CF Union 2021 Survey Results
  • ColdFusion Development: Less Coding for Faster Turnaround
  • Protected: State of the CF Union 2021 Survey Released
  • ColdFusion Server Crashing – First 3 Steps to Fix it Fast

Categories

  • ActionScript
  • Adobe CF Summit
  • Adobe CF Summit East
  • Adobe CF Summit East 2018
  • Adobe ColdFusion 11
  • Adobe ColdFusion 2020 Beta
  • Adobe ColdFusion 2021
  • Adobe ColdFusion Project Stratus
  • Adobe ColdFusion Security
  • AIR
  • Ajax
  • AngularJS
  • Announcement
  • API
  • Apollo
  • Auto Security Lockdown
  • AWS
  • C#
  • Certification
  • CF Alive
  • CF Alive Book
  • CF Alive Podcast
  • CF Camp
  • CF Developer week
  • CF Maintenance
  • CF Summit India
  • CF Tags
  • CF Training
  • CF Vs. Other Languages
  • CFEclipse
  • CFML
  • CFML Open- Source
  • CFObjective
  • cfquery
  • CFSummit
  • CFUnited
  • China Chopper
  • CIO
  • Classes
  • Client Highlights
  • ColdBox
  • ColdFusion
  • ColdFusion 2018
  • ColdFusion 2020
  • ColdFusion 2021
  • ColdFusion 9
  • ColdFusion community
  • ColdFusion Conference
  • ColdFusion Consulting
  • ColdFusion Developer
  • ColdFusion Development
  • ColdFusion Hosting
  • ColdFusion Roadmap
  • ColdFusion Security
  • ColdFusion Webinar
  • CommandBox
  • Conference
  • Cool Stuff
  • Culture
  • Cybercrime
  • Database
  • Development Approach
  • DevOps
  • Docker
  • Fixinator
  • Flex
  • Frameworks
  • Fusebox
  • FusionReactor
  • Futurology
  • Garbage Collector
  • Google Down
  • Into The Box Latam
  • IntoTheBox Conference
  • Java
  • JavaScript
  • JVM
  • Learn CFML
  • Learn ColdFusion
  • Legacy Code
  • Load Testing
  • Lucee
  • Management
  • MAX
  • MDCFUG Lunch
  • Microsoft Azure
  • Mindmapping
  • MockBox
  • Modernize ColdFusion
  • Monitoring
  • Muracon
  • NCDevCon
  • New Intern
  • News
  • Node.js
  • Open- Source
  • ORM
  • Ortus Developer Week
  • Ortus Roadshow
  • Performance
  • Performance Tuning
  • PHP
  • Productivity
  • Programming Languages
  • Project planning
  • Query of Queries
  • Scalability
  • Security
  • Server Crash
  • Server Software
  • Server Tuning
  • Social Media
  • Spiral Web
  • SQL
  • Success Story
  • Survey
  • Technology
  • TestBox
  • Tips
  • Transcript
  • Trapeze Development
  • Uncategorized
  • Web 2.0
  • Web Application
  • Web Server
  • Webinar
  • Webmail
  • What is ColdFusion?
  • Whole Brain Development
  • Women in Tech
  • Work From Home
  • Home
  • Services
  • About Us
  • CF Alive
    • CF Alive Book
    • CF Alive Inner Circle
    • CF Alive full resources cheatsheet
  • Blog
  • Podcast
    • Podcast Guest schedule
  • Contact
  • Sitemap

The ColdFusion Experts:
Develop, Secure, Optimize

TeraTech Inc
451 Hungerford Drive Suite 119
Rockville, MD 20850

Tel : +1 (301) 424 3903
Fax: +1 (301) 762 8185

Follow us on Facebook Follow us on LinkedIn Follow us on Twitter Follow us on Pinterest Follow us on YouTube

Copyright © 1998–2021 TeraTech Inc. All rights Reserved.