Hey fellow CF fans, it is Monday, and that means another blog entry. This time I’m throwing together a little code snippet that uses a little-known feature of CFQuery that was included in CF2021 but rarely talked about.

As you might know, when Adobe ColdFusion returns a query, it returns it in a special style of struct, which you can iterate over and just generally use to display data, do calculations, the whole bit. But sometimes, you just want to dump the return to screen, especially when it is tabular data. In the past, you would probably do something like this:

<table>
<cfoutput query="nameofquery">
<tr>
<td>#somevariable#</td>
<td>#anothervariable#</td>
</tr>
</cfoutput>
</table>

And guess what, that works! But what if you want more from your table? Like, for example, search? Pagination? STYLE! Well, in that case, you might choose to leverage the free DataTables plugin. What is neat about DT is that you can build a table, then apply DT to it and it will paginate it, the whole 9 yards. But DT has way more features than that. You can, as it happens, point it at some JSON and it will merrily iterate over it and build your table for you.

That’s where the new “returnType=’json/array'” feature of CFQuery comes in. Instead of returning as your standard, run of the mill query struct, ColdFusion will instead pull the resultset out of the structure (if you want the WHOLE THING in JSON, you would set ‘json/struct’) and return you back a lovely JSON object.

Their powers combined, you’ve got a nice quick way to huck a bunch of data into your page with all the bells and whistles that DataTables provides. Let’s grab some code and see what we got.

First, we’re going to build the endpoint. For simplicity’s sake, I’m just making this a .cfm page sitting in the same folder. You can make this a cfc, expose it as an endpoint, the works, but here we’re going easy. The name I’ve chosen is getData.cfm.

<cfscript>
resultArray = [];
structthing = {"item" = "item1", "notes" ="notes1", "done"="yes"}
arrayAppend(resultArray, structthing);
structthing = {"item" = "item2", "notes" ="notes2", "done"="no"}
arrayAppend(resultArray, structthing);
structthing = {"item" = "item3", "notes" ="notes3", "done"="no"}
arrayAppend(resultArray, structthing);
structthing = {"item" = "item4", "notes" ="notes4", "done"="yes"}
arrayAppend(resultArray, structthing);
function arrayToQuery(data) {
    return data.reduce(function(accumulator, element) {
        element.each(function(key) {
            if (!accumulator.keyExists(key)) {
                accumulator.addColumn(key, []);
            }
        });
        accumulator.addRow(element);
        return accumulator;
    }, QueryNew(""));
}
resultQuery = arrayToQuery(resultArray);
</cfscript>
<cfquery name="converttojson" returntype="json/array" dbtype="query">
SELECT * from resultQuery
</cfquery>
<cfscript>
writeOutput(converttojson);
</cfscript>

OK so that’s a lot, but the good news is, you can ignore the first… 23 lines or so of this. What I did in the first part is to build a fake query using some code I shamelessly stole from John Whish (THANKS JOHN!).

That code just takes an array and generates a CF query result object out of it. The important part is the tag based portion. Apologies for breaking out of script there, but cfquery just works so nicely in tags. So you can see, all I am doing is grabbing the entire contents of my fake query and outputting it as a new QoQ named “converttojson”. Then I dump it to screen. If you point your browser at that page, you’ll just get a bunch of JSON.

My evil plan is coming right along!

For the next part, we do have to know what columns we’ll be getting back, so we can properly build out part of the table. So let’s look at the display code.