What Is Query of Queries in ColdFusion?

Definition

A Query of Queries (often abbreviated QoQ) in ColdFusion is a way to run SQL-like statements against query objects already in memory, rather than hitting an external database again. In practice, you execute a second SELECT statement over the result of a previous cfquery (or a manually created query), using the ColdFusion in-memory SQL engine. This lets you filter, sort, group, and transform data you already fetched, without another round-trip to the database.

---

How Query of Queries Works

The Concept

• You run a normal database query (or build a query object) and get a query recordset in ColdFusion.
• You then run a second query where the query object acts like a table. That second query uses cfquery with dbtype=”query” (or queryExecute with equivalent options).
• ColdFusion evaluates the SQL against the in-memory dataset, returning a new query object.

This pattern is often described as running an in-memory SQL SELECT on data you already have.

Basic Syntax

• Tag-based:

  • SELECT col1, col2 FROM qSource WHERE col3 > 10 ORDER BY col1

• Script-based (queryExecute):

  • qResult = queryExecute(
  • “SELECT col1, col2 FROM qSource WHERE col3 > :threshold ORDER BY col1”,
  • { threshold = { value=10, cfsqltype=”cf_sql_integer” } },
  • { dbtype=”query” }
  • );

Key points:

• Set dbtype=”query”.
• Use the name of your query object (e.g., qSource) in the FROM clause.
• Use parameters to avoid string concatenation and to improve safety.

Supported SQL (Subset)

QoQ supports a subset of SQL commonly used for data manipulation:

• SELECT with column lists, aliases, and expressions.
• WHERE with operators (=, <>, >, <, BETWEEN, LIKE, IN).
• ORDER BY, GROUP BY, and sometimes HAVING (check your version).
• Aggregate functions like COUNT, SUM, AVG, MIN, MAX.

It does not support DML such as INSERT/UPDATE/DELETE in QoQ. JOIN support is limited or unavailable depending on ColdFusion version; many developers treat JOINs as unsupported for QoQ and use workarounds (see Best practices).

---

Step-by-Step Example (Practical Use Case)

Scenario

You fetched a large employee list once from the database and need to create multiple, differently filtered reports without repeated database hits.

1. Initial database query:

• SELECT id, department, title, salary, hireDate
• FROM Employees
• WHERE active = 1

2. Build a department-specific report with QoQ:

• SELECT id, title, salary, hireDate
• FROM qEmployees
• WHERE department =
• ORDER BY salary DESC

3. Get salary statistics with QoQ:

• SELECT COUNT(id) AS employeeCount,
• AVG(salary) AS avgSalary,
• MIN(hireDate) AS earliestHire
• FROM qEmployees
• WHERE department =

4. Filter by date range with parameters (script-based):

• startDate = createDate(2020,1,1);
• qRecentIT = queryExecute(
• “SELECT id, title, salary, hireDate
• FROM qEmployees
• WHERE department = :dept AND hireDate >= :startDate
• ORDER BY hireDate DESC”,
• {
• dept = { value=”IT”, cfsqltype=”cf_sql_varchar” },
• startDate = { value=startDate, cfsqltype=”cf_sql_timestamp” }
• },
• { dbtype=”query” }
• );

Outcome: You only queried the database once. All other slices and summaries came from in-memory filtering and aggregation.

---

Common Use Cases

• Data sharding for reports: Create multiple filtered views (e.g., by region, department) from one base query.
• Client-side pagination: Slice a dataset into pages without re-querying the DB.
• Aggregations and rollups: Compute totals, averages, and counts.
• Post-processing API or CSV data: Convert external data into a query object (QueryNew or spreadsheet-to-query) and run SQL-like filters on it.
• Rapid Prototyping: Adjust conditions quickly during development without touching Database schema or adding views.
• Merging heterogenous sources: Combine and normalize datasets in memory (e.g., database + CSV), then slice with QoQ.

---

Benefits and Trade-Offs

Advantages

• Performance via fewer round-trips: For repeated views of the same data, QoQ can be faster than hitting the DB again.
• Familiar SQL Syntax: Leverages developers’ SQL knowledge within CFML.
• Useful for non-database sources: Brings SQL-like querying to In-memory data built from APIs, files, or custom arrays/structs (once converted to a query object).
• Keeps DB load down: Useful when DB connections are constrained or when you need quick iterations.

Limitations

• Subset of SQL: Not all SQL Features are available. JOINs are limited or unsupported; complex expressions may not function identically to your RDBMS.
• Memory-bound: Datasets must fit in server memory; very large queries can increase heap usage and GC pressure.
• No physical indexes: Sorting and filtering are O(n log n) or O(n); can be slower than a well-indexed database for large datasets.
• Different type semantics: Date/time, numeric, and null handling can differ from your RDBMS. Testing is important.
• Still needs parameterization: String concatenation can expose you to injection risks even in memory; use cfqueryparam or parameter structs.

---

Working With Data Types and Functions

Data Types

• Dates and times: Prefer using ColdFusion date objects and pass parameters as cf_sql_date or cf_sql_timestamp. For literals, consider CreateODBCDate(CreateDate(…)) or CreateODBCDateTime.
• Numbers: Ensure numeric columns are truly numeric in the query. If you built the query with strings, explicit casting or careful Comparison may be required.
• Booleans: Treat as numeric (0/1) or strings depending on how the query was created.

Helpful Built-ins

• QueryNew, QueryAddColumn, QuerySetCell: Build or transform query objects programmatically.
• ValueList and ListToArray: Quick extraction or transformation of column values.
• isNumeric, isDate, LSParseNumber, ParseDateTime: Normalize types before QoQ.

---

Best practices

Know When To Use QoQ vs Database

• Use QoQ when:

  • You already fetched the dataset and need multiple filtered/sorted views.
  • Data originates outside the DB (files, APIs) and you want SQL-style processing.
  • You’re Prototyping reports or transformations.

• Use the database when:

  • Data is large, joins are needed, or you rely on indexes for Performance.
  • Complex analytics or window functions are required.
  • You can push work to the DB layer for better Scalability and accuracy.

Optimize for Performance

• Limit initial dataset size: Fetch only columns and rows you need. Smaller inputs make QoQ faster.
• Reduce repeated scans: Chain QoQ steps thoughtfully to minimize total work.
• Parameterize: Use cfqueryparam or queryExecute parameter structs for safer and potentially faster execution.
• Cache smartly: Consider caching the initial query result to amortize the cost across many QoQs.
• Clean up: If you create large intermediate queries, release references to allow GC.

Structure Your QoQ Code

• Name queries clearly (qBase, qFiltered, qGrouped).
• Keep QoQ SQL readable and consistent with your application’s SQL style.
• Document assumptions about data types and supported SQL Features.
• Avoid relying on ambiguous behavior (e.g., implicit type coercion).

Workarounds for Missing JOINs

• Pre-join at the database: Fetch a combined dataset once, then slice with QoQ.
• Manual join in CFML:
  • Create hash maps (structs keyed by ID) for smaller lookup tables.
  • Loop through base query and add columns via QuerySetCell or a new query built from arrays, then QoQ the enriched dataset.
• Nested QoQ:
  • Run an initial QoQ to reduce rows (e.g., filter), then a second QoQ for aggregations.

Maintain Data integrity

• Normalize types early: If reading CSV, parse dates and numbers upon import.
• Handle null-ish values carefully: Decide on placeholders and be consistent (e.g., empty strings vs zero vs actual null support in your CF version).

---

Syntax Patterns and Examples

Classic Filtering and Ordering

• SELECT id, title, salary
• FROM qEmployees
• WHERE department =
• ORDER BY salary DESC
• LIMIT 10

Note: LIMIT support can vary. If unsupported, emulate with row_number logic after selecting or by slicing the query in CFML.

Grouping and Aggregation

• SELECT department, COUNT(id) AS cnt, SUM(salary) AS totalSalary, AVG(salary) AS avgSalary
• FROM qEmployees
• GROUP BY department
• ORDER BY totalSalary DESC

Script Example with queryExecute

• sql = “
• SELECT department, COUNT(*) AS cnt
• FROM qEmployees
• WHERE hireDate >= :cutoff
• GROUP BY department
• “;
• params = { cutoff = { value=createDate(2022,1,1), cfsqltype=”cf_sql_date” } };
• options = { dbtype=”query” };
• qRecentHires = queryExecute(sql, params, options);

---

Comparisons and Alternatives

• Database-side views or temp tables:

  • Pros: Full SQL power, indexing, better for Big data.
  • Cons: Requires DB changes or connections; higher round trips.

• Arrays of structs with arrayFilter/arraySort:

  • Pros: Flexible, functional style, no SQL subset constraints.
  • Cons: More code, less declarative than SQL.

• CFML data-lifting then QoQ:

  • Pros: Balance of flexibility and readability; leverage SQL familiarity.
  • Cons: Memory use; limited SQL features.

---

Key Points

• QoQ = SQL-like querying on ColdFusion query objects already in memory.
• Use dbtype=”query” in cfquery or queryExecute options.
• Great for secondary filtering, sorting, grouping, and reporting without another DB hit.
• Mind the subset of SQL and potential lack of JOIN support; plan workarounds.
• Treat QoQ code with the same rigor as DB SQL: parameterize and test types.
• Keep datasets reasonably small and well-typed for performance and correctness.

---

Troubleshooting Tips

• Unexpected empty results:

  • Verify column names and case sensitivity.
  • Check value types (string vs number vs date).

• Date comparisons not matching:

  • Ensure both sides are dates/timestamps; use cfqueryparam with cf_sql_date/cf_sql_timestamp.

• “Feature not supported” errors:

  • Simplify SQL to the supported subset. Replace JOINs with pre-joined data or CFML lookups.

• Memory spikes:

  • Reduce base query size, avoid creating many large intermediates, or move work back to the database.

---

Real-World Example: Reporting Dashboard

A sales dashboard needs multiple widgets:

• Top 10 products by revenue this month.
• Sales by region and by sales rep.
• Year-over-year comparisons.

Approach:

1. Pull a month’s worth of sales records once (qSales).
2. Build qTopProducts via QoQ with GROUP BY productId, SUM(amount), ORDER BY sum DESC.
3. Build qByRegion and qByRep via QoQ grouped aggregations.
4. For YoY, either fetch last year’s data once or cache it and run QoQ slices.

Result: The UI remains responsive by avoiding repeated DB queries; the dashboard re-composes views from a single in-memory dataset with minimal overhead.

---

FAQ

What ColdFusion tags or functions are used for Query of Queries?

Use with the attribute dbtype=”query”, or use queryExecute(sql, params, { dbtype=”query” }). The FROM clause references the name of your in-memory query object, not a database table.

Does Query of Queries support JOIN?

JOIN support is limited and historically treated as unsupported. Most teams either pre-join data in the database or perform manual joins in CFML before running QoQ. Always check the documentation for your ColdFusion version if you plan to rely on joins.

Is QoQ faster than querying the database again?

It depends. For small to medium datasets already in memory, QoQ can be faster because there’s no network round-trip. For large datasets or complex operations, a database with indexes will usually outperform QoQ.

How do I prevent injection in QoQ?

Parameterize QoQ just like database queries. Use cfqueryparam in tag-based code or parameter structs in queryExecute. Avoid concatenating user input into SQL strings.

Can I use QoQ on data that didn’t come from a database?

Yes. You can build a query object using QueryNew/QueryAddColumn or load data from spreadsheets/CSV into a query, then run QoQ to filter, sort, and aggregate that in-memory dataset.