Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Shawn Grigson
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Shawn Grigson@shawngrig )

Returning CFQuery / Query Results As Arrays In Lucee 5.3.2.77

By Ben Nadel on
Tags: ColdFusion

Anyone who has used ColdFusion is very familiar with the Query object that is returned from the cfquery tag. But, if you are using Lucee 5+, the cfquery tag has a fun little efficiency built into it: the returnType attribute. The returnType attribute accepts the values, query (the default), struct, array, and array_of_entity. And, gets the Lucee CFML engine to perform a little post-query-processing on your behalf. There's no magic here; but, if you often convert your queries into Arrays - like I generally do - getting the Lucee CFML engine to do it for you makes life just a little bit sweeter.

One place that I would find this particularly helpful is when I am writing a SQL query that joins multiple tables together. As of late, I've gotten into the habit of prefixing the columns from each joined table so that each set of collocated columns can be clearly identified:

SELECT
	( a.foo ) AS a_foo,
	( b.foo ) AS b_foo
FROM
	table_a a
INNER JOIN
	table_b b
ON
	a.someID = b.someID

Then, on the server-side, mapping each set of collocated records onto an individual ColdFusion struct that I can consume in my View template:

<cfscript>

	rc.data = [];

	for ( var record in recordSet ) {

		rc.data.append({
			a: {
				foo: record.a_foo
			},
			b: {
				foo: record.b_foo
			}
		});

	}

</cfscript>

As you can see, in order to do this, I have to allocate an empty Array; then, I have to loop over the Query results, break-apart each record, and append the transformed object onto the Array. If my Query came back as an Array already, I could convert this transformation into a single .map() call.

To see this in action, I've put together a queryExecute() call that joins two tables. I then .map() and .sort() the resultant Array:

<cfscript>

	// When I write a query that pulls data back from multiples tables, I've gotten
	// into the habit of prefixing each column with a table-identifier (ex, "user_"); and
	// then, on the server-side, separating the collocated columns into separate Structs.
	// By returning the query results AS AN ARRAY, this just becomes one step easier
	// because I can quickly map(), sort(), filter(), etc.
	results = queryExecute(
		"
			SELECT
				( u.id ) AS user_id,
				( u.name ) AS user_name,
				( u.email ) AS user_email,
				( a.lastLoginAt ) AS account_lastLoginAt,
				( a.createdAt ) AS account_createdAt
			FROM
				user u
			INNER JOIN
				account a
			ON
				(
						u.id IN ( :idList )
					AND
						a.userID = u.id
				)
			;
		",
		{
			idList: {
				value: "1,2,3,4",
				sqlType: "integer",
				list: true
			}
		},
		{
			datasource: "testing",
			returnType: "array" // <=== Query will be returned as Array-of-Structs.
		}
	);

	// At this point, results is a QueryArray implementation, which is some sort of
	// slightly specialized Array (which includes SQL meta-data in its data-dump). This
	// means that "results" has all of the expected Array member methods like sort(),
	// map(), filter(), reduce(), etc.
	mappedResults = results
		// Map the prefixed columns onto separate Structs.
		.map(
			( record ) => {

				return({
					user: {
						id: record.user_id,
						name: record.user_name,
						email: record.user_email
					},
					account: {
						lastLoginAt: record.account_lastLoginAt,
						createdAt: record.account_createdAt
					}
				});

			}
		)
		// Sort the query results.
		// --
		// WHY NOT SORT IN THE DATABASE? The "name" column isn't an indexed column. As
		// such, sorting on the "name" column requires the database to do extra work
		// since it can't leverage the natural ordering of the index. And, since I have
		// MANY MORE APP SERVERS (N) than I do databases (1), I'd rather distribute the
		// additional load to the app servers. It's just a MICRO-OPTIMIZATION for queries
		// that are "hot paths" through the system.
		.sort(
			( a, b ) => {

				return( compareNoCase( a.user.name, b.user.name ) );

			}
		)
	;

	dump( label = "Query Array Results", var = mappedResults );

</cfscript>

As you can see, I am passing returnType: "array" to the queryExecute() function configuration. This returns the recordset as an Array of Structs. Which means that I can immediately call .map() and .sort() (among many other member methods) directly on the results.

Now, if we run this Lucee CFML page, we get the following output:

queryExecute() returned the recordset as an Array of Structs due to the returnType of array in Lucee CFML 5.3.2.77.

As you can see, by using a returnType of array, Lucee CFML returns the query results as an Array of Strucuts. This means that I get to start calling member-methods like .map() and .sort() directly on the returned value!

Like I said above, there's no "magic" here. Lucee is really just doing something declaratively that I normally would have done imperatively. So, really, this is just a small efficiency that removes a few lines of code. But, every little efficiently is a win. And, hopefully, the native Lucee implementation that converts the recordset into an Array of Structs is just a little bit faster than anything I would have written myself.

Epilogue On returnType Of struct And columnName

If you specify a returnType of struct, Lucee will convert the query recordset into a Struct in which each record is keyed by the given columnName. At first, I was very excited about this because I thought it would be akin to the .groupBy(key) concept that we've seen in libraries like Lodash. However, Lucee does not "group" records that are keyed by the same value. Instead, it just overwrites the value with whichever record came last.


Reader Comments

Hey Ben,

nice post. I actually love the returntype="struct" way more. Since I use it to lookup values by ID. If you want to have Lucee group your results, you should create a columnkey just for that. I know that overwriting the key is perhaps a problem, but then again, it might be just the wrong key you are using. All in all a struct is a key value construct and the key is unique.
See you at CFCamp :)

Gert

Reply to this Comment

@Gert,

If I know that the IDs are going to be unique, I think the struct type will be cool. But, my common use-case is that I am performing an in-app "join" instead of doing it in my queries. So, my code will often look like this:

var things = getThings();
var thingsIDList = valueList( things, "id" );

var otherThings = getOtherThings( thingsIDList );
var otherThingsIndex = utilities.groupBy( otherThings, "thingID" );

That last line ... utilites.groupBy( otherThings, "thingID" ) ... is what I would try to replace with the struct type. But, it will depend on whether its a one-to-one or a many-to-one. If its a one-to-one, then returntype of struct will be good! But, if one-to-many, I'll have to keep using a custom function.

Regardless, this stuff is cool!

Re: CFCamp -- will be checking my calendar ;)

Reply to this Comment

@Ben

Not dis-proportionally excited at all, in my opinion. I was also pretty excited about this feature and submitted a feature request to the CFWheels Repo [1] as a result. As move further and further away from ACF and begin to explore LCF more closely, I'm really loving how thoughtful their implementation is. They add a ton of sugar to the language which makes working with it joyful. Super stoked to see you highlighting some of them.

[1] https://github.com/cfwheels/cfwheels/issues/945

Reply to this Comment

@Chris,

I definitely agree. A lot of subtle, but really nice things! The challenge for me is going to be finding the features so that I know I can leverage them :D

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.