ColdFusion 10 - ColdFusion Query Of Queries vs. Functional Programming
Posted April 10, 2012 at 10:36 AM by Ben Nadel
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!
Reader 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).
It yould be interesting to do a performance comparison of both methods for a tad complicated scenarios
Nice article.
Like Nelle, which method is the best in terms of performance ?
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.
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.
