May 16, 2022
Code Snippet: Datatables from CFQuery JSON returntype
Comments
(1)
May 16, 2022
Code Snippet: Datatables from CFQuery JSON returntype
ColdFusion developer for 20+ years, professional experience in 10 other languages & frameworks. Artist, nerd, Jeep enthusiast.
Newbie 35 posts
Followers: 25 people
(1)

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.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Datatable Demo</title>
    <link href="https://cdn.datatables.net/1.11.1/css/jquery.dataTables.min.css" rel="stylesheet" />
</head>
<body>
<table id="apitable" name="apitable" class="dataTable" style="width: 100%">
    <thead>
        <tr>
            <th>Item</th>
            <th>Notes</th>
            <th>Done?</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>
<script
  src="https://code.jquery.com/jquery-3.6.0.js"
  integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk="
  crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.11.1/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function() {
    $('#apitable').DataTable( {
        ajax: {
            url: 'getData.cfm',
            dataSrc: ''
        },
        columns: [
            {data: 'item'},
            {data: 'notes'},
            {data: 'done'}
        ]
    } );
} );
</script>
</body>
</html>
Yeehaw, that’s a lot of code. I mean, it really isn’t when you consider what you end up with.
Starting at the top, I’m including the CSS for DataTables. That is, of course, critical to having things look & align correctly. Usually I will also include something else like Bootstrap, but again, simplicity.
Then a very critical part, the table. You’ll note the table has an id, a class, but most importantly, it has a proper structure, including a thead and tbody. DataTables will not have a good time when handed an improperly structured table (and I’ve done that… more than once). Note that I have 3 columns.
Moving down, we include JQuery. The full name of DataTables is “JQuery DataTables” or at least it was, once. I believe there’s a version of DT you use that doesn’t require JQuery, but I’m old school so we’re gonna just use the basic stuff, which does require JQuery. Finally, we include the JS for DataTables itself.
OK, now the good bit. We load DataTable, target the id of the table we built earlier (the id I chose was “apitable” and then we pass it some params. The first is url, and we aim that at our getData.cfm file. Again, this could be… well, anything that returns JSON. The dataSrc param lets DT know if there’s a subset it needs to target of the JSON. In this case, no, so I pass it a blank string. Then we pass it the columns we’re looking for, and these must match the keys of the JSON string you’re returning.
And then… nothing. You’re done. You’re all set, you’re dynamically generating tabular data from JSON. And instead of having to do a serializeJSON() dance, you’re getting it right from the CFQuery itself.
1 Comment
2022-05-18 14:34:26
2022-05-18 14:34:26

See Mark, I knew I should have asked you to take a crack at my last app.  Where were you a couple months back with this.  Now you can fix the rest of my app while I convert my existing code to using this trick.  This will be really cool, the next step is you integrate HighCharts to pull from this Data Table you got going on and boom, you got that one query json return making a table and a chart.  As a happy byproduct you’ll have done all the work I did on my app the hard way and I can steal it.  Nice post.

Like
Add Comment