August 14, 2020
Quick Tip: Returning a query as an array of structures in ColdFusion 2018 using reduce() and the fat arrow operator
Comments
(2)
August 14, 2020
Quick Tip: Returning a query as an array of structures in ColdFusion 2018 using reduce() and the fat arrow operator
Wizard 43 posts
Followers: 23 people
(2)

In this quick tip, I’m going to show you how to use the reduce() function in tandem with the fat arrow operator in ColdFusion 2018 to return a query as an array of structures.  In Lucee, returning an array of structs from a query is as simple as adding a returntype=”array” attribute to the <cfquery> tag.  This doesn’t exist in ColdFusion.  This example executes a query like normal, but applies the reduce() function on the return to convert the query object into an array of structures.

public array function getUser (numeric id) {
    local.SQL = "SELECT users.* FROM users WHERE (1 = 1)";
    local.Parameters = {};

if (!isNull(arguments.id)) {
local.SQL &= ” AND (id = :id)”;
local.Parameters.insert( ‘id’, { cfsqltype: ‘integer’, value: arguments.id } );
}

local.Result = queryExecute(local.SQL, local.Parameters);

return local.Result.reduce((result, row) => {
result.append(row);
return result;
}, []);
}

Please note; the fat arrow operator was introduces in ColdFusion 2018 update 5 and ColdFusion 2016 update 12.  Installations before this hot fix will not work.

2 Comments
2020-12-04 21:24:33
2020-12-04 21:24:33

Hey, David, I just thought to test that code against CF2016 (latest current update, 15) and it did NOT work. You mentioned that you thought it was in both CF2018 (post update 5) and CF2016 (post update 12), but I’m afraid that does not seem to be the case. All the looking into fat arrows/lambda’s in the CF docs seem to mention only CF2018.

Just clarifying for readers, as much as anything. But again, great stuff for those on CF2018 (until they get to CF2021 when it’s still easier with returntype=”array” on cfquery/queryexecute).

Like
2020-12-04 20:53:53
2020-12-04 20:53:53

Great stuff, David. For readers, we should note that in CF2021, Adobe HAS added the returntype=”array” (and even “json/array”) for cfquery and queryexecute. Of course, CF2021 was released 3 months after this post, and the public beta would start for another week.

But for those on CF2018 or 2016, this is indeed a cool alternative until they may upgrade.

And if anyone may want to see a working example of this approach, I have a working example at the cffiddle site. We can’t use a real cfquery there, so I use the querynew function to create a “fake” query. (And I don’t bother with implementation as a function or the additional query parameter insertion.)

So it’s just a few lines of code, like David shows above.

Like
Add Comment