Returning CFQuery / Query Results As Structs In Lucee 5.3.6.61
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:
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
CFQueryParamtag. This is a hot tip that I picked up from Julian Halliwell. Lucee CFML will automatically treat theCFQueryParamas alist="true"parameter-binding since thevalueattribute 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.
Want to use code from this post? Check out the license.
Reader Comments
ah, the array for cfqueryparam is a nice trick, I just updated the Lucee docs to reflect this https://github.com/lucee/lucee-docs/pull/877
https://docs.lucee.org/reference/tags/queryparam.html
there is also some newish support for indexed queries, since 5.3.4.23
https://docs.lucee.org/reference/tags/query.html#attribute-indexname
https://docs.lucee.org/reference/functions/queryrowbyindex.html
https://docs.lucee.org/reference/functions/querygetcellbyindex.html
https://docs.lucee.org/reference/functions/queryrowdatabyindex.html
But the implementation is somewhat buggy https://luceeserver.atlassian.net/browse/LDEV-2803
@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:
... which would return:
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).
Just stumbled on that post (Thanks Phil) and saw the array trick for cfqueryparam!
But, need to mention that you need the sql type of the values in the array for it to work. I guess you should always have the type specified, but it is not required if just a string, an integer, etc...
@Frédéric,
I also vaguely remember that there was some recent breaking change about passing in an Array as the value. I think it might be what you are talking about - that if you omit the type, something funky can happen. Definitely, I would always supply a type, though.