Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Dee Sadler
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Dee Sadler@DeeSadler )

Replacing ColdFusion Query-Of-Queries (QoQ) With Array Functions In Lucee CFML 5.2.9.31

By Ben Nadel on
Tags: ColdFusion, SQL

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:

Query of Queries ORDER BY replaced with Array sort in Lucee CFML.

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:

Query of Queries WHERE IN replaced with Array filter in Lucee CFML.

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:

Query of Queries UNION ALL replaced with Array merge in Lucee CFML.

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:

Query of Queries WHERE NOT LIKE replaced with Array filter in Lucee CFML.

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:

Query of Queries WHERE and ORDER BY replaced with Array filter and sort in Lucee CFML.

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:

Query of Queries dynamic ORDER BY replaced with Array sort in Lucee CFML.

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:

Query of Queries MAX ROWS replaced with Array first in Lucee CFML.

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.



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:

QofQ

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.

Reply to this Comment

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

Reply to this Comment

I've just looked at the Adobe:

QofQ

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:

YEAR()
MONTH()
DAY()

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.

Reply to this Comment

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?

Reply to this Comment

@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:

<cfscript>
	records.each(
		( record ) => {
			
			record.calculatedColumn = /* ... sub-query logic for this record. */
		
		}
	);
</cfscript>

Just thinking out-loud, not knowing entirely how you are using this stuff.

Reply to this Comment

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

Reply to this Comment

@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 Query object 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

Reply to this Comment

But, now that Lucee makes closures and fat-arrow syntax so easy

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

@All,

Here is a quick follow-up post on replacing ColdFusion Query-of-Queries INNER JOIN and LEFT OUTER JOIN concepts using Array functions in Lucee CFML:

https://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.

Reply to this Comment

@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:

https://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.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.