Replacing ColdFusion Query-Of-Queries (QoQ) With Array Functions In Lucee CFML 5.2.9.31
Last week, on the Modernize Or Die ColdFusion Podcast, Gavin Pickin and Brad Wood reminded us that ColdFusion is not a query engine. And that - despite the simplicity of the ColdFusion Query-of-Queries (QoQ) - there are much more performant ways to achieve the same results. There's no doubt that Query-of-Queries are amazing; and that they are part of what makes ColdFusion so powerful. But, as the CFML language has become more robust, Query-of-Queries can more easily be replaced with faster, native Array manipulation. To demonstrate, I wanted to dig through the InVision codebase, locate a number of old Query-of-Query instances, and showcase how those in-memory queries can be modernized with various Array methods.
NOTE: All of the data in these demos are defined as Arrays. This assumes that you are returning your queries as Arrays (which is an awesome feature of Lucee CFML)!
The first instance I found was a basic ORDER BY query in which the sorting of the recordset was pulled back into the ColdFusion application in an attempt to reduce the processing needed by the MySQL server. Unless the ORDER BY columns are based on an index, the database server has to do work to implement the sort. As such, it can make sense to offload the sort to the application servers, which are horizontally scaled.
<cfscript>
/*
<cfquery dbtype="query" name="local.sortedResult">
SELECT
*
FROM
result
ORDER BY
termLength ASC,
maxProjectCount ASC
</cfquery>
*/
results = [
{ id: 1, name: "Sarah", isBFF: 1, friendshipSince: "2012-01-17" },
{ id: 2, name: "Tim", isBFF: 0, friendshipSince: "2018-04-12" },
{ id: 3, name: "Kit", isBFF: 0, friendshipSince: "2016-11-28" },
{ id: 4, name: "Kim", isBFF: 1, friendshipSince: "2008-06-22" },
{ id: 5, name: "Hanna", isBFF: 0, friendshipSince: "2010-05-30" }
];
sortedResults = results.sort(
( a, b ) => {
// ORDER BY isBFF DESC
if ( a.isBFF != b.isBFF ) {
return( a.isBFF ? -1 : 1 );
}
// ORDER BY friendshipSince ASC
return( a.friendshipSince.compareNoCase( b.friendshipSince ) );
}
);
dump(
label = "ORDER BY isBFF DESC, friendshipSince ASC",
var = sortedResults
);
</cfscript>
Here, the compound ORDER BY clause has been replaced with a native .sort() Array method. In order to implement the compound sort, we just have to have a cascading comparison in the sort operator. And, when we run the above ColdFusion code, we get the following output:
In the next example, we were using a WHERE IN clause to limit the results based on a given list of IDs. Assuming that the list of IDs is dynamic, we can replace this by indexing the values in a ColdFusion Struct. And then, checking the keys in that Struct within a .filter() method:
<cfscript>
/*
<cfquery name="local.filteredResults" dbtype="query">
SELECT
*
FROM
[local].result
WHERE
targetScreenID IN ( #screenIDList#, 0 )
</cfquery>
*/
results = [
{ id: 1, name: "Sarah", friendTypeID: 1 },
{ id: 2, name: "Tim", friendTypeID: 1 },
{ id: 3, name: "Kim", friendTypeID: 3 },
{ id: 4, name: "Dave", friendTypeID: 2 },
{ id: 5, name: "Mark", friendTypeID: 2 },
{ id: 6, name: "Libby", friendTypeID: 3 }
];
targetFriendTypeIDList = "2,3";
targetFriendTypeIndex = indexArray( targetFriendTypeIDList.listToArray() );
filteredResults = results.filter(
( a ) => {
// WHERE friendTypeID IN ( 2, 3 )
return( targetFriendTypeIndex.keyExists( a.friendTypeID ) );
}
);
dump(
label = "WHERE friendTypeID IN ( 2, 3 )",
var = filteredResults
);
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
/**
* I return a struct that uses the values as keys so that individual values can be
* checked for existence.
*
* @value I am the collection being indexed.
*/
public struct function indexArray( required array values ) {
var index = {};
for ( var value in values ) {
index[ value ] = value;
}
return( index );
}
</cfscript>
As you can see, I've created a User Defined Function (UDF), indexArray(), that takes the values in the list of IDs and converts them to Struct. I then use that Struct to filter the results via .filter(). And, when we run the above ColdFusion code, we get the following output:
In the next example, we were using a UNION ALL to merge two different Queries. Again, this is being done in an attempt to offload CPU load from the database server. Returning two queries from the database, instead of one, returns the same amount of data; however, it moves the burden of the "temp table" to the horizontally-scaled application servers. A UNION ALL is possibly the easiest query to replace:
<cfscript>
/*
<cfquery name="local.results" dbtype="query">
SELECT
*
FROM
[local].qryTeamPlanMembers
UNION ALL
SELECT
*
FROM
[local].qryPossibleProjectMembers
</cfquery>
*/
productsA = [
{ id: 1, name: "Product A", price: 1.00 },
{ id: 2, name: "Product B", price: 1.25 },
{ id: 3, name: "Product C", price: 1.50 },
{ id: 4, name: "Product D", price: 1.75 }
];
productsB = [
{ id: 5, name: "Product E", price: 2.00 },
{ id: 6, name: "Product F", price: 2.50 }
];
// UNION ALL.
results = productsA.merge( productsB );
dump(
label = "UNION ALL",
var = results
);
</cfscript>
Here, we're just taking one recordset and calling .merge() on it. This results in a new Array that contains the values from both arrays. And, when we run this ColdFusion code, we get the following output:
In ColdFusion, not every "query object" comes from a database. Some queries are [optionally] returned from other data-access methods, like directoryList(). In the next example, we were using such a "directory list" query in order to filter on filename pattern matching. But, this can be easily replaced with .filter() and some Regular Expression (RegEx) patterns:
<cfscript>
/*
<cfquery name="local.files" dbtype="query">
SELECT
directory,
name
FROM
[local].files
WHERE
<!---
Make sure we don't pull in the Thumbnail file. This file will simply
be deleted along with the demo teardown.
--->
name NOT LIKE '%__demo_thumbnail__%'
<!--- Make sure that we're only pulling in image files (not hidden files). --->
AND
(
LOWER( name ) LIKE '%.jpg'
OR
LOWER( name ) LIKE '%.jpeg'
OR
LOWER( name ) LIKE '%.png'
OR
LOWER( name ) LIKE '%.gif'
)
ORDER BY
name ASC
</cfquery>
*/
results = [
{ path: "/some/path/.DS_Store" },
{ path: "/some/path/sample--cat.png" },
{ path: "/some/path/sample--dog.png" },
{ path: "/some/path/cat.jpg" },
{ path: "/some/path/dog.webp" }, // Unsupported file format.
{ path: "/some/path/elk.jpeg" },
{ path: "/some/path/ferret.png" },
{ path: "/some/path/gibbon.gif" },
{ path: "/some/path/hippo.bmp" }, // Unsupported file format.
{ path: "/some/path/iguana.tiff" } // Unsupported file format.
];
filteredResults = results
.filter(
( a ) => {
var filename = getFileFromPath( a.path );
// NOTE LIKE '%sample--%'
if ( filename.findNoCase( "sample--" ) ) {
return( false );
}
// LIKE '%.png' / '%.jpg' / '%.jpeg' / '%.gif'
return( filename.reFindNoCase( "\.(gif|png|jpe?g)$" ) );
}
)
.sort(
( a, b ) => {
// ORDER BY path ASC
return( a.path.compareNoCase( b.path ) );
}
)
;
dump(
label = "WHERE LIKE",
var = filteredResults
);
</cfscript>
As you can see, we're using the .reFindNoCase() RegEx method to reduce the results down to a set of acceptable filenames. And then, we're using the .compareNoCase() method to sort on the path. As it turns out, .compareNoCase() is a perfect fit for array-based sorting since it return -1, 0, and 1 (ish), which is exactly what a sort comparison needs. And, when we run the above ColdFusion code, we get the following output:
In the next example, we just have another instance of the WHERE and ORDER BY clauses being moved off of the database server and into the application tier in an attempt to reuse a query object instead of making two different queries against the database. As with the earlier examples, our WHERE is replaced with a .filter() and our ORDER BY is replaced with a .sort():
<cfscript>
/*
<cfquery name="local.sortedResult" dbtype="query">
SELECT
*
FROM
[local].result
WHERE
isArchived = 0
AND
<!--- Don't return the mobile icon (that's broken out in the UI). --->
screenTypeID != 2
ORDER BY
sort ASC,
id ASC
</cfquery>
*/
results = [
{ id: 1, name: "Sarah", isBFF: 1, friendshipSince: "2012-01-17" },
{ id: 2, name: "Tim", isBFF: 0, friendshipSince: "2018-04-12" },
{ id: 3, name: "Kit", isBFF: 0, friendshipSince: "2016-11-28" },
{ id: 4, name: "Kim", isBFF: 1, friendshipSince: "2008-06-22" },
{ id: 5, name: "Hanna", isBFF: 0, friendshipSince: "2010-05-30" },
{ id: 6, name: "Kim", isBFF: 1, friendshipSince: "2009-10-19" }
];
sortedResults = results
.filter(
( a ) => {
// WHERE isBFF AND friendshipSince < '2010-01-01'
return( a.isBFF && ( a.friendshipSince < "2010-01-01" ) );
}
)
.sort(
( a, b ) => {
// ORDER BY name ASC
if ( a.name != b.name ) {
return( a.name.compareNoCase( b.name ) );
}
// ORDER BY id DESC
return( ( a.id > b.id ) ? -1 : 1 );
}
)
;
dump(
label = "WHERE and ORDER BY name ASC, id DESC",
var = sortedResults
);
</cfscript>
Compound condition in the WHERE and SORT clauses simply become cascading checks in the relevant .filter() and .sort() methods. And, when we run the above ColdFusion code, we get the following output:
In the following example, we have a dynamic ORDER BY based on a user-provided value. This could have been done in the original query; but, again, this ORDER BY was moved from the database server to the horizontally-scaled application tier in order to improve performance.
<cfscript>
/*
<cfquery name="local.filteredResults" dbtype="query">
SELECT
*
FROM
[local].results
WHERE
screenTypeID = 1
AND
isArchived = 0
ORDER BY
<!--- Manual sort. --->
<cfif ( projectSortTypeID eq 1 )>
sort ASC,
id ASC
<!--- Alphabetical sort. --->
<cfelse>
name ASC,
id ASC
</cfif>
</cfquery>
*/
results = [
{ id: 1, name: "Sarah", isBFF: 1, friendshipSince: "2012-01-17" },
{ id: 2, name: "Tim", isBFF: 0, friendshipSince: "2008-06-22" },
{ id: 3, name: "Kit", isBFF: 0, friendshipSince: "2016-11-28" },
{ id: 4, name: "Kim", isBFF: 1, friendshipSince: "2008-06-22" },
{ id: 5, name: "Hanna", isBFF: 0, friendshipSince: "2010-05-30" }
];
sortedResults = results.sort(
( a, b ) => {
// ORDER BY name ASC, id ASC
if ( false ) {
// ORDER BY name ASC
if ( a.name != b.name ) {
return( a.name.compareNoCase( b.name ) );
}
// ORDER BY id ASC
return( ( a.id < b.id ) ? -1 : 1 );
// ORDER BY friendshipSince ASC, name ASC
} else {
// ORDER BY friendshipSince ASC
if ( a.friendshipSince != b.friendshipSince ) {
return( a.friendshipSince.compareNoCase( b.friendshipSince ) );
}
// ORDER BY name ASC
return( a.name.compareNoCase( b.name ) );
}
}
);
dump(
label = "Conditional ORDER BY",
var = sortedResults
);
</cfscript>
As you can see, the dynamic ORDER BY clause is replaced by a .sort() that has some logic branching. And, when we run this ColdFusion code, we get the following output:
For one final example, I came across some really silly CFML code that was using the maxrows attribute of the CFQuery tag in order to pluck the first row. If we return our queries as Arrays, this is as simple as calling .first() on the collection:
<cfscript>
/*
<cffunction name="getTopRow" access="private">
<cfargument name="theQuery" type="query"/>
<cfset var q = "">
<cfquery name="q" maxrows="1" dbtype="query">
select * from arguments.thequery
</cfquery>
<cfreturn q/>
</cffunction>
*/
results = [
{ id: 1, name: "Project Tagging" },
{ id: 2, name: "Global Search" },
{ id: 3, name: "Slides" },
{ id: 4, name: "Download Images" },
{ id: 5, name: "Export as ZIP" }
];
// MAX ROWS = 1
filteredResults = results.first();
dump(
label = "MAX ROWS = 1",
var = filteredResults
);
</cfscript>
As you can see, in order to get the first row, we only need to get the first item in the array. Though, to be clear, this is not strictly the same. If using a Query-of-Queries, this would still work if the original query was empty. When using Arrays, however, attempting to call .first() on an empty-array will result in a ColdFusion error:
Cannot return first element of array; array is empty
However, since our demo Array is populated, running the above ColdFusion code results in the following output:
Those examples represent the use-cases that we had for Query-of-Queries at InVision. But, as you can see, now that the CFML language is more robust, there's really less of need to use Query-of-Queries. Basically, all of the same functionality can be replaced with faster, more efficient Array manipulation methods (like .sort() and .filter()).
Query-of-Queries also supports SQL constructs like GROUP BY and INNER JOIN. These can certainly be replaced with native ColdFusion logic. But, to be sure, it takes a more significant amount of code to replace such abstractions. Perhaps I can demonstrate that in a follow-up post.
Want to use code from this post? Check out the license.
Reader Comments
This is a really interesting exploration of the power of arrays and their native manipulation functions.
However, I really wish ACF/Lucee would update:
So, that we can unleash the full power of the SQL language. Like using Aggregation, Sub queries and SQL functions like CONCAT(), YEAR() etc...
It really miffs me off that QofQ has such a limited subset of SQL functionality.
@Charles,
As a follow-up, I want to try and look at some aggregation stuff in Arrays. But, I think QoQ does do some GROUP BY and aggregation. I haven't used it in a while to do that; but, 99% sure some of that exists.
I've just looked at the Adobe:
Documentation. I reckon I need to take back what I have just said. It looks like it does have aggregation. And there is string concatenation!
But still no sub queries
What I can't work out, is whether the list of reserved words in:
https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html
Can be used in QofQ, because I see functions like:
Does this mean we can use the SQL version of these functions? Or is it saying we can't use these functions?
Anyway, it looks like QofQ has quite a lot more power than I thought.
I just know in the past, that when I have tried using certain SQL constructs that I get errors.
I think it is maybe the fact that QofQ doesn't support sub queries. I use these quite a lot in the SELECT clauses...
But, maybe, there is a good reason, why this can't be achieved in QofQ?
@Charles,
For a sub-query, you could just map one collection onto another. Or, just iterate over it and run a secondary query as the sub-query. Like:
Just thinking out-loud, not knowing entirely how you are using this stuff.
Great post, Ben. i have always been a fan of QofQ's but I think you have to be careful of performance with large result sets, especially if you're applying a complex filter. The only really annoying thing QofQ lacked for me was an outer join. ColdBox has a cbCommons module which has utilities like doLeftOuterJoin() and rotateQuery() that filled in some gaps.
https://www.forgebox.io/view/cbcommons
@Brad,
Absolutely -- when I found out about Query-of-Queries, it was an amazing moment. I still remember the first time I saw someone using it in a query and I couldn't figure out what the heck was going on because they were using a method that wasn't supported by the database. I was like HOW IS THIS POSSIBLE?!?!?!
Let's be honest, ColdFusion
Queryobject is one of the reasons CF was so awesome. Looping over it was a breeze and a game-changer.But, now that Lucee makes closures and fat-arrow syntax so easy ... the tides are shifting :D
To be fair, Adobe introduced fat arrows in 2018 update 5 and once the next Adobe updater comes out with some remaining fixes for it, it will actually work better than Lucee's fat arrows! The Adobe team was super responsive in getting those fixed up.
But yes, you are correct, there are nice functional ways to do a lot of those things. In fact, looking back through that QueryHelper CFC I mentioned above, I'd say about half of the functions in there are probably superseded by nice member functions these days like filter, sorting, and aggregating.
@Brad,
I jumped from ColdFusion 10 to Lucee 5.2 -- so, I never have a good instinct about what Adobe ColdFusion had in the meantime. Certainly, I am not trying to disparage them. It's just that Lucee is primarily the "modern" platform that I know at this point.
We use Lucee as middle-ware between (several) bespoke and enterprise applications. So it is a vital and common requirement to join data from multiple sources (MySQL, MSSQL, Sybase, FoxPRO... even scheduled auto-imports of xls and xlsx files).
Using QoQ can be dreadful - and requires painful, kludgey, slow workarounds like Brad mentions with Left Outer Joins, etc. - but we've yet to see a better way to do this, especially when multiple parties want to see this aggregated data in real time on their dashboards.
We'd sure love to have a more robust solution and are very open to suggestions on how to improve upon QoQ solutions ot the above...
@Andrew,
I totally get it. Joining multiple data-sources is actually one of the most powerful use-cases for QoQ. I don't have the example any more; but, one of the reasons I've had to do that is when joining a master database with a read-replica. In such cases, "stale data" is more acceptable in one of the tables; but, I can't use them in the same
<cfquery>since they come from different data-sources. But, I can pull them back individually and then "join" them together in a Query-of-Queries.That said, I have a pattern that I use a lot when performing an "in app" JOIN. I'll try to write it up for tomorrow.
@Ben,
Great - looking forward to it!
@All,
Here is a quick follow-up post on replacing ColdFusion Query-of-Queries
INNER JOINandLEFT OUTER JOINconcepts using Array functions in Lucee CFML:www.bennadel.com/blog/3716-replacing-coldfusion-query-of-query-qoq-inner-join-and-left-outer-join-with-array-functions-in-lucee-cfml-5-3-3-62.htm
Though, to be clear, this technique is applicable for more than just query-of-queries. In fact, I use this technique all the time to move CPU load out of the single database instance and into the horizontally-tiered application layer.
@All,
A couple of people on Twitter asked me about the relative performance of ColdFusion Query-of-Queries and Array-based data collation. To be clear, both options are fast; but, Array-based methods are faster. In most context, there is not going to be any pragmatic difference. However, when trying to optimize a hot pathway that has been measured to be a performance bottleneck, it could be a viable optimization:
www.bennadel.com/blog/3717-superficial-performance-comparison-between-coldfusion-query-of-queries-qoq-and-array-functions-in-lucee-cfml-5-3-3-62.htm
Take this performance test with a grain of salt, as you should any other performance comparison.