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

Returning CFQuery / Query Results As Arrays In Lucee 5.3.2.77

By
Published in — Comments (7)

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.

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

Short link: https://bennadel.com/3654

Reader Comments

7 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

15,848 Comments

@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 ;)

238 Comments

@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

15,848 Comments

@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

22 Comments

You can do this in MSSQL and MariaDB already directly in the DB using new json objects...

eg:

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name], ( SELECT pt.pet_id AS id, pt.pet_name AS name FROM @Pets pt WHERE pt.pet_owner=pr.person_id FOR JSON PATH ) AS [person.pet] FROM @Persons pr FOR JSON PATH, ROOT('pet owners')

to output:

{
"pet owners":
[
{
"person":
{
"id":2,"name":"Jack","pet":
[
{"id":4,"name":"Bug"},
{"id":5,"name":"Feature"}
]
}
},
{
"person":
{
"id":3,"name":"Jill","pet":
{"id":6,"name":"Fiend"}
}
}
]
}

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017

15,848 Comments

@Dawesi,

Interesting. I'm not super familiar with MSSQL syntax, so I am not sure which parts of that query are specifically for JSON and which are just MSSQL's way of aliasing columns. It seems, though, like you have to really change the query itself to return JSON. The nice thing about Lucee is that you don't change the query at all -- you just have to tell it to create an array out of the return values.

Hmm, I am not sure what happened with the formatting of your comment. It looks like somehow the <code> didn't generate properly from your fenced code block. Somehow, P-tags ended up inside the Code-tag. Very odd. I'll have to figure out what happened.

Oh my chickens, this post is old!
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