July 12, 2019
Converting an array of structs to a query dynamically
Comments
(7)
July 12, 2019
Converting an array of structs to a query dynamically
I try to bend the internet to my will.
Wizard 34 posts
Followers: 23 people
(7)

I was working with some JSON data in Javascript and wanted to view the data in a table layout so I could quickly scan it to see what I was working with. This is easily done in a browser using console.table. For example, using the following Javascript code:

// Javascript
data = [
    { i: 1, label: "One" },
    { i: 2, label: "Two", foo: "Foo" },
    { i: 3, label: "Three", bar: "Bar" },
    { i: 4, label: "Four", foo: "Foo", bar: "Bar" }
];

console.table(data);

I get a nice compact view of the data in my browser console:

output from console.table(data)

This got me thinking about how you could dump out an array of structs in a compact form using CFML.

Here’s what the standard dump of that array in CFML looks like:

writedump of array of structs

Not too bad to read, but as we get more rows and/or more keys, it’s going to get much harder to visual scan.

I decided that a dump of CFML’s query object is essentially what we are after, so just need to convert the array to a query. As each element in the array has different keys, this needs to be done dynamically. Here’s what I came up with:

// CFML
data = [
    { i: 1, label: "One" },
    { i: 2, label: "Two", foo: "Foo" },
    { i: 3, label: "Three", bar: "Bar" },
    { i: 4, label: "Four", foo: "Foo", bar: "Bar" }
];
  
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(""));
}

// writeDump(data);
writeDump(arrayToQuery(data));

The output from this looks like:

writedump of array as query

I’m not intending to use this code in production, it was more of a coding exercise, but thought I’d share.

Runnable code here if you want to hack about with it.
https://cffiddle.org/app/file?filepath=d3656d85-050d-4a86-b26f-9797073991d9/3688841c-1312-47b4-9f85-00880b379a54/7c2c9541-cf21-4ff0-9795-7dcda91b963b.cfm

7 Comments
2020-05-15 15:10:35
2020-05-15 15:10:35

@Jeff Coughlin Thanks for your comment, I’m aware of the data parameter for QueryNew. I didn’t use that here as you need to know the columns in advance before you can pass in the array. So sure, I could have used arrayReduce to find all the fields names, then used that do the `QueryNew(fields_found_in_array)`, but dynamically adding the cols seemed to be a much neater way to handle it.

 

Like
(1)
>
aliaspooryorik
's comment
2020-05-21 21:08:44
2020-05-21 21:08:44
>
aliaspooryorik
's comment

This was patched in CF2018 update 5 (allowing you to just send in an array of structs and it essentially does all the same code you wrote in the backend).

queryNew(data);

However, assuming you don’t have CF2018 update 5+ and you don’t have the column names, then you’re out of luck without looping and to get the data like you did. In that case, it’s a cool function (thanks!)

Just for kicks I wrote some code to work with queryNew, an array value, and getting the column names. It worked on the first try, but without realizing it I essentially just rewrote your code (facepalm). 

accumulator = “”;
columns = data.reduce(function(accumulator, item) {
item.each(function(key) {
if (!listFind(accumulator, key)) {
accumulator = listAppend(accumulator, key);
}
});
return accumulator;
});

writeDump(queryNew(columnNames = columns, data = data));

Like
2020-05-09 03:08:20
2020-05-09 03:08:20

This functionality is already built into CF (CF10+):

writeDump(queryNew(“i,label,foo,bar”, “integer,varchar,varchar,varchar”, data));

For more info, see the rowData attribute in queryNew() here: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querynew.html

Like
2019-07-21 09:52:22
2019-07-21 09:52:22

Very nice thinking outside the box! This will be handy, thanks! 🙂

Like
2019-07-18 14:28:11
2019-07-18 14:28:11

Yes, very nice. Thanks, John (aka poor yorik. “Alas”, I wonder how many get the quip in your “alias”!)

About the code, I hope you’ll submit it to the cflib site (cflib.org). Folks do still search there for possible CFML solutions to problems. 

You’ll see a link there for submitting content, and indeed it’s now hosted on github (so submissions are by PR to that now). Ray is still the project owner, and though there are a couple of PRs outstanding from 2017, it’s worth a shot to see if it would get posted.

Thanks of course for the cffiddle.org link, in the meantime.

Like
(1)
>
Charlie Arehart
's comment
2019-07-20 09:10:40
2019-07-20 09:10:40
>
Charlie Arehart
's comment

Hi Charlie,

Happy to add it to cflib – looks like it’s a Jekyll site now so will have to find some time to figure that out!

Like
2019-07-18 03:41:57
2019-07-18 03:41:57

Simple and elegant.  Great use of arrayReduce().

Like
(1)
Add Comment