Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

Returning CFQuery / Query Results As Structs In Lucee 5.3.6.61

By Ben Nadel on
Tags: ColdFusion

A few months ago, I looked at returning CFQuery results as an Array in Lucee CFML; and, in the comments of that post, Gert Franz pointed out that Lucee could also return CFQuery results as a Struct in which a given column would be used as the look-up key in said Struct. Mapping a query onto a Struct is actually a technique that I use a lot in my ColdFusion code, typically with some sort of .indexBy() or .groupBy() User-Defined Function (UDF). As such, I wanted to take a moment and look at how I might replace my custom functions with this built-in query feature in Lucee CFML 5.3.6.61.

To invoke this feature in Lucee CFML, all you have to do is provide the CFQuery tag with two additional attributes:

  • returntype="struct"
  • columnkey="{{ columnName }}"

This will tell the Lucee runtime to iterate over the underlying result-set and map each row to a Struct-key using the columnkey as the row identifier. To see this in action, here's a query that looks up Club Membership counts and then maps them over to the userID associated with the count:

<cfscript>

	```
	<cfquery name="results" returntype="struct" columnkey="userID">
		SELECT
			cm.userID,
			COUNT( * ) AS clubCount
		FROM
			club_membership cm
		WHERE
			cm.userID >= 25
		GROUP BY
			cm.userID
	</cfquery>
	```

	// NOTE: Using Struct.append() here simply to remove the query meta-data from the
	// dump-output (to make the output cleaner).
	dump(
		var = {}.append( results ),
		label = "Query as Struct"
	);

</cfscript>

This is going to return a Struct in which the userID column of each row is used as the key in the resultant Struct. And, when we run this ColdFusion code, we get the following output:

A CFQuery returned as a Struct, indexed-by userID in Lucee CFML.

In a ColdFusion application, I often use this type of data-structure to move INNER JOIN relationships and sub-queries into the application code. Not that there's anything wrong with INNER JOIN and sub-queries; I just have cases in which I like to simplify my SQL queries and break down my data-aggregation into individual steps.

To see how this returntype="struct" might be used to replace a sub-query, let's look at Users who belong to Clubs. In the following code, I'm going to get a subset of application users; and, then for each user, output the number of Clubs in which they are a member. To do this, I'm going to use the returntype="struct" to generate a map of Club-counts associated with each user:

<cfscript>

	users = getUsers();
	// Now that we have the users, let's go back to the database and see how many clubs
	// have been joined by the given user. This function will return a Struct that is
	// keyed-by the userID.
	clubCounts = getClubCountIndex( pluck( users, "id" ) );

	for ( user in users ) {

		// The underlying query is only returning rows that HAVE AT LEAST ONE club-
		// membership. As such, users without a club won't be keyed in the resultant
		// Struct. To that end, we'll default to zero in order to make the subsequent
		// logic a bit easier.
		count = ( clubCounts[ user.id ]?.clubCount ?: 0 );

		if ( count ) {

			echo( "* #user.name# is part of #count# clubs! <br />" );

		} else {

			echo( "* #user.name# hasn't joined any clubs yet. <br />" );

		}

	}

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	/**
	* I get the users for the demo.
	*/
	public array function getUsers() {

		```
		<cfquery name="local.results" returntype="array">
			SELECT
				u.id,
				u.name
			FROM
				user u
			WHERE
				u.id >= 24 -- Test users.
		</cfquery>
		```

		return( results );

	}


	/**
	* I get the club-count index. This returns a STRUCT that is keyed-off the userID and
	* includes records that contain the ".clubCount" for that user.
	* 
	* CAUTION: Only users that have joined 1 OR MORE CLUBS will be in the index.
	* 
	* @userIDs I am the collection of userIDs to inspect.
	*/
	public struct function getClubCountIndex( required array userIDs ) {

		// Notice that we are using "Struct" as returntype here - this will return a
		// Struct in which each key (columnkey) references a SINGLE ROW in the underlying
		// query.
		// --
		// CAUTION: The "last row wins" in this case - it is not "grouping" rows, it is
		// simply indexing them by the given columnkey. As such, any duplicate row will
		// overwrite a previously-indexed row.
		```
		<cfquery name="local.results" returntype="struct" columnkey="userID">
			SELECT
				cm.userID,
				COUNT( * ) AS clubCount
			FROM
				club_membership cm
			WHERE
				cm.userID IN ( <cfqueryparam value="#userIDs#" sqltype="integer" /> )
			GROUP BY
				cm.userID
		</cfquery>
		```

		return( results );

	}


	/**
	* I return a new collection of values plucked from the given collection.
	* 
	* @collection I am the collection being inspected.
	* @key I am the key being plucked from each item in the collection.
	*/
	public array function pluck(
		required array collection,
		required string key
		) {

		// NOTE: I normally don't use this type of concise syntax in my code. I'm only
		// including it here for a little variety; and, to deemphasize this part of the
		// code.
		return( collection.map( ( item ) => item[ key ] ) );

	}

</cfscript>

As you can see, once I have the users that I want to inspect, I pluck the ID values from the set of users; and then I use these IDs to gather all the Club-count values. The resultant Club-counts Struct is keyed on the userID column.

ASIDE: Notice that I'm passing an Array of user IDs into my CFQueryParam tag. This is a hot tip that I picked up from Julian Halliwell. Lucee CFML will automatically treat the CFQueryParam as a list="true" parameter-binding since the value attribute in an Array. Nice!!

Of course, users that don't have any Club-memberships will be omitted from the resultant Struct. As such, I'm using the safe-navigation operator in conjunction with the Elvis operator to provide a default Count value for each user in the output.

And, when we run this ColdFusion code, we get the following output:

  • Sarah Smith hasn't joined any clubs yet.
  • Branch Smith is part of 4 clubs!
  • Bianca Smith hasn't joined any clubs yet.
  • Jon Smith is part of 3 clubs!
  • Paige Smith is part of 2 clubs!
  • Anna Smith is part of 4 clubs!
  • Steven Smith is part of 3 clubs!
  • Ronan Smith is part of 2 clubs!

Awesome! Worked like a charm.

Another aspect of this return-type that isn't immediately obvious is that the resultant Struct is an Ordered Struct (see Lucee Dev Forum). Which means that a for-in loop will iterate over the Struct in the same order that the keys were assigned. Which means, if the underlying query had an ORDER BY clause (or an implicit ordering based on an Index), the for-in loop will match that ordering.

Again, there's nothing wrong with performing sub-queries in the Database - Databases are totes-awesome for this kind of thing. But, in algorithms where data aggregation needs to be broken-down into individual steps for "reasons", I am loving that Lucee CFML can index a query for me. This will almost certainly replace my .indexQueryBy() calls using what is certain to be a faster, more native implementation.



Reader Comments

Reply to this Comment

@Zac,

Interesting, I hadn't seen this indexed-stuff before. I'll have to dig in more deeply to understand what it's doing (since the docs just say indexname, but don't say what it is, ha).

One thing that could be really cool re: returning the query as a Struct, would be the ability to group duplicate rows. So something like this:

<cfquery returntype="struct" columnkey="userID" group="true">

... which would return:

interface QueryStruct {
	[ key: string ]: QueryRow[ ]; // Each key-value is an ARRAY of rows.
}

Then, this could both act as an "index by" operation or a "group by" operation. I use both of these on a regular basis (but do so using UDFs on the returned query).

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Blog
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.