Skip to main content
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with: Tom Chiverton
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with: Tom Chiverton

ColdFusion 10 - ColdFusion Query Of Queries vs. Functional Programming

By
Published in Comments (5)

For years, I have been a huge fan of the ColdFusion query object and the power of ColdFusion query-of-queries. While at times, they can present some funky and irksome data-type behaviors, these query-based operations have made storing, accessing, and mutating internal data structures a thing of beauty. With the introduction of ColdFusion 10, however, I've found myself switching over much more to CFScript and functional programming. As such, I thought it would be interesting to do a quick CRUD (Create, Read, Update, Delete) comparison between ColdFusion query-of-queries and the functional-style programming that has been facilitated by ColdFusion 10's new function expressions and closures.

NOTE: At the time of this writing, ColdFusion 10 was in public beta.

For this exploration, I'm going to create a ColdFusion query object manually and then perform Create, Read, Update, and Delete operations on it. For the sake of the demo, I am not going to bother using JavaCast() to store my query values. Typically, this would be an important step since the ColdFusion query object relies on the underlying Java data types when sorting, comparing, and aggregating column values. But, since those data-type behaviors are not necessarily relevant for this demo, I'm going to skimp on the query-of-queries "best practices" and just get down to a syntactic comparison between approaches.

The first half of the code is the ColdFusion query-of-queries approach; the latter half of the code is a CFScript-based replication of the first half using a functional programming approach:

<!---
	Build up a query manually so that we can explore the access and
	mutation of the query using query of queries.

	NOTE: I am not bothering with using JavaCast() for this demo.
	Ordinarilly, you'd want to JavaCast() *every* value that you
	manually put into a query object since the underlying technology
	relies on the comparison of Java values.
--->
<cfset friends = queryNew(
	"id, name, age",
	"cf_sql_integer, cf_sql_varchar, cf_sql_integer",
	[
		[ 1, "Sarah", 37 ],
		[ 2, "Tricia", 42 ],
		[ 3, "Kim", 25 ],
		[ 4, "Joanna", 31 ],
		[ 5, "Kit", 35 ]
	]
) />


<!---
	Now that we have our ColdFusion query object, let's demonstrate
	how to execute the following query Query-of-Query style
	programming:

	- Create
	- Read
	- Update
	- Delete
--->


<!---
	CREATE a record to the query. With ColdFusion 10's augmented
	queryAddRow() method, this is now a trivla exercise.
--->
<cfset queryAddRow(
	friends,
	[ 6, "Anna", 33 ]
) />


<!---
	DELETE a record from the query. With the ColdFusion query-of-
	queries, this is basically a SELECT that overrides the original
	value.
--->
<cfquery name="friends" dbtype="query">
	SELECT
		id,
		name,
		age
	FROM
		friends
	WHERE
		id != 3
</cfquery>


<!---
	UPDATE a record in the query. Since the ColdFusion query-of-query
	syntax does not support an UPDATE clause, we need to actually
	find the target row and then update it manually.
--->
<cfset targetRow = arrayFind( friends[ "id" ], 4 ) />

<!---
	Now that we have the target row, we can update the appropriate
	values with the row.
--->
<cfset friends[ "age" ][ targetRow ] = 40 />


<!--- READ a particular row given an name. --->
<cfquery name="anna" dbtype="query">
	SELECT
		id,
		name,
		age
	FROM
		friends
	WHERE
		name = 'Anna'
</cfquery>



<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->



<!---
	Let's now switch over to CFScript since function expressions
	and closures can not be defined when in a CFML tag context.
--->
<cfscript>


	// Build up our friends object. With the new ColdFusion 10
	// queryNew() enhancements, creating a query is basically the
	// same as creating collections of objects; except with an
	// object, the name-value pairs needs to be defined explicitly.
	friends = [
		{
			id: 1,
			name: "Sarah",
			age: 37
		},
		{
			id: 2,
			name: "Tricia",
			age: 42
		},
		{
			id: 3,
			name: "Kim",
			age: 25
		},
		{
			id: 4,
			name: "Joanna",
			age: 31
		},
		{
			id: 5,
			name: "Kit",
			age: 35
		}
	];


	// Now that we have our ColdFusion query object, let's
	// demonstrate how to execute the following query Query-of-Query
	// style programming:
	//
	// - Create
	// - Read
	// - Update
	// - Delete


	// CREATE a new entry in the collection. Here, we basically just
	// need to append a new object.
	arrayAppend(
		friends,
		{
			id: 6,
			name: "Anna",
			age: 33
		}
	);


	// DELETE a entry from the collection. For this, we can think of
	// the delete as being a filtering of the collection in which the
	// item we want to get rid of doesn't contian the target value.
	friends = arrayFilter(
		friends,
		function( friend ){

			// Return TRUE for all record that don't have ID = 3.
			return( friend.id != 3 );

		}
	);


	// UPDATE an entry in the collection. For this, we can use pretty
	// much the same approach since our query-of-query approach used
	// the query as an array (like our current context.)
	targetIndex = arrayFind(
		friends,
		function( friend ){

			// Return TRUE if the target ID is 4 - then we'll know
			// that we have the right index.
			return( friend.id == 4 );

		}
	);

	// Now that we have the target index, update the age value.
	friends[ targetIndex ].age = 40;


	// READ a particular entry given a name. For this, we'll have to
	// figure out which entry has the given value.
	targetIndex = arrayFind(
		friends,
		function( friend ){

			// Return TRUE if the target name is Anna.
			return( friend.name == "Anna" );

		}
	);

	// Now that we have the target index - get the entry.
	anna = friends[ targetIndex ];


</cfscript>

Granted, there are many ways to accomplish the same behaviors; but, at a glance, the ColdFusion query-of-query approach appears to have basically the same level of complexity as the functional programming approach. Or rather, with ColdFusion 10's new function expressions, the functional programming approach is finally as simple as a the ColdFusion query-of-queries approach. In fact, both approaches have become more straightforward with ColdFusion 10's language enhancements.

While I am very pleased to know that "collection" manipulation is now as easy with functional programming as it is with query-of-queries programming, this does little to ease my mind. This is great for demos and small proof-of-concept applications where query objects can be used to persist values in memory; but, when it comes to production applications that interact with databases, queries are primarily used for database access and manipulation. In such cases, SQL still needs to be written and queries still need to be used.

Unless, of course, you start using ORM (Object-Relational-Mapping) for all of your database access. Then, you can use object-based collections rather than query-based collections. But, that's a whole other discussion!

Want to use code from this post? Check out the license.

Reader Comments

8 Comments

Thanks for the example.

It's nice to see a summary in action, even when it demonstrates one of CF's ongoing shortcomings (some features are only available in one of both modes).

15,811 Comments

As far as performance is concerned, I believe that Query-of-Queries will be orders of magnitude slower. It's engaging a much more complex engine under the hood - remember it has to parse and execute SQL statements in a context that is not a full-fledged database management system. Looping over arrays and calling functions is going to be WAY faster than executing SQL queries.

That said, for small things, I doubt you'd see any performance difference. This is one of those things that probably only shows up when you're doing test cases with thousands of records.

19 Comments

I've found that using the simplest data type possible for a given task is important with large sets of data in cold fusion.

Example, loading hundreds of instantiated cfcs as an array of items (loaded from a DB by a PK) versus just storing and manipulating a copy of the query itself.

If I want each cfc object to have a copy of it's own methods for that item's manipulation, then the array of CFCs is the way to go, but this has a huge amount of overhead and is poor performing in large numbers (one of the reasons I do not use orm for anything with large result sets).

If all I want is the data, I just pass around a pointer (reference) to the query, I don't need the overhead of instantiation.

With large datasets (over 200 records), I find it best to use the query and write the methods as if it was using a static scope, rather than use ORM.

ORM has it's place i'm sure, but I don't often find myself needing the overhead involved with it.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel