How to output cfquery to array?

[pirlo89]

Hello everyone,

I'm trying to output my cfquery to array. I need something like this:

[code]

<cfset myarray=arrayNew(2)>

<cfloop query="qryCustomers">

     <cfset myarray = [{"CustomerID":ID,"Name":FirstLast}]>

</cfloop>

[/code]

I tried this code but that did not work. Error always indicate that something is wrong in my cfset where I have my ID and Customer name together. I'm not sure either if I can do this in cold fusion. I have to put these to values together because I have to use them later to compare with other values from second query. If anyone can tell me what is wrong in my code I would appreciate that. Thanks in advance.

[EddieLotter]

One way to do it:

<cfloop query="qryCustomers">

     <cfset myarray[#currentRow#][1] = #ID#>

     <cfset myarray[#currentRow#][2] = #FirstLast#>

</cfloop>

Cheers

Eddie

[pirlo89]

How I can create a structure first and then add it to an array?

So I tried something like this but still does not work.

[CODE]

<cfset recs = StructNew()>

   <cfloop query="qryTest">

        <cfset recs[currentRow] = StructNew()>

        <cfset recs[currentRow].Customer = ' ' & qryTest.CustomerID>

         <cfset recs[currentRow].Number = ' ' & qryTest.Number>

  </cfloop>

  <cfset records = [{CustomerID = "#CustomerID#",Number = "#Number#"}] />

  <cfset fncResults.data = recs>

[/CODE]

[EddieLotter]

That's a different question, but here is how you do it:

<cfset myarray = arrayNew(1)>
<cfloop query="qryTest">
 <cfset recs = structNew()>
 <cfset recs.Customer = CustomerID>
 <cfset recs.Number = Number>
 <cfset arrayAppend(myarray, recs)>
</cfloop>
<cfdump var="#myarray#">

(Sorry about the poor formatting.)

Cheers

Eddie

[BKBK]

Eddie shows how to do it yourself. Painlessly.

[drummingsticks]

I created the following until hopefully Adobe ColdFusion might adopt a cool returntype attribute feature that Lucee offers to turn the query type into an array or structs.  Until then or maybe never:

<cfscript>
queryToAOS(yourQueryVariableHere);

function queryToAOS (qryData) {
	local.aos = [];
	for (local.i = 1; local.i <= qryData.recordCount; local.i++) {
		local.aos.append(queryGetRow(qryData, local.i));
	}
	return local.aos;
}
</cfscript>

[Charlie Arehart]

Folks (especially to drummingsticks' last comment), some good news is that CF2021 has indeed addressed this, adding the ability to return a query resultset as an array or indeed a json array, whether using cfquery or queryexecute. See the docs for the new ReturnType as an attribute for cfquery or an argument for queryexecute.

If someone might wonder, "how would we ever know that without constantly watching the docs for changes", I will note that it's also listed as one of the new features for CF2021 on this features page: 
https://helpx.adobe.com/coldfusion/user-guide.html/coldfusion/using/whats-new.ug.html 

Hope that's helpful.

[Robin James]

How would I merge two CF2021 query array resultsets to create a multi-dimensional array?  If query1 is the primary dataset and query2 uses the junction table for one to many for one of the columns in query1?  Would I need to resort back to looping to create this?

For example, I copied this image because it depicts what I'm trying to do where values are from the second junction table.  

Thank you!

[Robin James]

"nested" array might be the more appropriate term

[BKBK]

Hi @Robin James , I am not aware of any ColdFusion function that can reproduce that combination of arrays and structs. I think you have to resort to looping. Tedious, but that has the added advantage that you will create exactly what you want.

[Charlie Arehart]

Here's yet another solution, for those on CF2018 (not CF2016 or earlier), indeed a one-liner, using the reduce and fat arrow functional programming constructs, which Adobe offered starting in CF2018 update 5 (in Sep 2019). 

David Byers did a blog post showing the concept and code, and here is a still simpler working example I just created on the cffiddle site. 

[I had originally written that this was also in CF2016 update 12, based on what David had written in his post. But right after posting my comment here, I thought to do a check against CF2016's latest update--as offered on that cffiddle site--and sadly it did NOT work. And my looking at docs elsewhere about these fat arrow/lambda's, they indeed mention only CF2018.)

Anyway, to show it here more simply in the context of pirlo89's original post and code above (which granted is from 2015), it would be (using tags as they did):

<cfset myarray=qryCustomers.reduce((result, row) => {result.append(row);return result;}, []);

Sure, that's ugly and even obtuse to those not familar with functional programming (which is supposed to make things "simpler"). But bottom line, it's literally as simple as appending ".reduce((result, row) => {result.append(row);return result;}, []);" to any queryname.

Hope that helps folks on CF2018, until they may get to 2021 where it's that simpler returntype="array" that I mentioned yesterday. 🙂

[Larry C Lyons]

Depending on the version of CF you're working with there's a very simple solution in CF2021 on. You could always just use the returnType option in your query declaration. For instance with the CFScript version:

QueryExecute("SELECT foo FROM bar ,{},{returnType="array"});

or the CFTag version:

<cfquery name="thisQry" datasource="#application.theDSN#" result="results" returntype="array">
SELECT foo FROM bar
</cfquery>

The returnType="array" attribute, which allows the CFQuery tag and the queryExecute() function to return an Array-of-Structs instead of a Query object. This might be the simplest approach.