portal entry

select a category, or use search below
(searches all categories and all time range)

Converting an array of structs to a query dynamically

| View in Portal
July 12, 2019 04:57:25 PM GMT
<p>An exercise in converting an array of structs to a query dynamically so you can dump out the data in a compact form</p>
<p>The post <a rel="nofollow" href="https://coldfusion.adobe.com/2019/07/converting-array-structs-query-dynamically/">Converting an array of structs to a query dynamically</a> appeared first on <a rel="nofollow" href="https://coldfusion.adobe.com">ColdFusion</a>.</p>
Labels: Blog, ColdFusion, Modern CFML, 2018, blog, modern cfml


Simple and elegant.  Great use of arrayReduce().
Comment by David Byers
2167 | July 18, 2019 03:41:57 AM GMT
<p>Yes, very nice. Thanks, John (aka poor yorik. “Alas”, I wonder how many get the quip in your “alias”!)</p><p>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. </p><p>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.</p><p>Thanks of course for the cffiddle.org link, in the meantime.</p>
Comment by Charlie Arehart
2168 | July 18, 2019 02:28:11 PM GMT
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!
Comment by aliaspooryorik
2172 | July 20, 2019 09:10:40 AM GMT
Very nice thinking outside the box! This will be handy, thanks! :)<span></span>
Comment by Aaron Neff
2176 | July 21, 2019 09:52:22 AM GMT
This functionality is already built into CF (CF10+): <strong>writeDump(queryNew("i,label,foo,bar", "integer,varchar,varchar,varchar", data));</strong> For more info, see the rowData attribute in queryNew() here: <a href="https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querynew.html" rel="nofollow">https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querynew.html</a>
Comment by Jeff Coughlin
4750 | May 09, 2020 03:08:20 AM GMT
<p>@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.</p><p> </p>
Comment by aliaspooryorik
4751 | May 15, 2020 03:10:35 PM GMT
This was patched in <strong>CF2018 update 5</strong> (allowing you to just send in an array of structs and it essentially does all the same code you wrote in the backend). <strong>queryNew(data);</strong> 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). <span></span> <strong>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)); </strong>
Comment by Jeff Coughlin
4759 | May 21, 2020 09:08:44 PM GMT