UDF: ArrayCollectionToQuery
November 05 2009 by 
Adam
Sometimes I find myself sending a query object from ColdFusion to Flex, for example, to bind to a DataGrid, and then I want to edit that query object and return it to ColdFusion to be saved in the database. Unfortunately, Queries are only a data type in ColdFusion. When sent to Flex, they get mapped to an ArrayCollection, and when you send that ArrayCollection back to ColdFusion, it isn't automatically converted back to a Query object (though lord knows that would be nice!).
It's certainly possible to just treat that ArrayCollection as an array of structures — because that's what it is — and loop over it to extract the data you want. Sure. Be my guest. But ColdFusion has so many great functions designed to make working with Queries easier. Wouldn't it be great if you could just convert that ArrayCollection back to a Query?
That's why I created this UDF, and I intend to submit it to CFLib once I know it's solid. I wanted to put this out to the community though to make sure it covers all of the necessary data types and doesn't have any errors. Can you see anything I've missed?
Below is the code for the function as well as a unit test. To see how it handles bad/unexpected data, you can do something wacky like change one of the values inside the structs to be a struct instead of the simple value it is now.
<cffunction name="arrayCollectionToQuery">
<cfargument name="arrayColl" type="Array" required="true" />
<cfset var qResult = '' />
<cfset var columnList = structKeyList(arrayColl[1]) />
<cfset var typeList = ''/>
<cfset var numericType = ''/>
<cfset var k = '' />
<cfset var i = 0 />
<cfloop collection="#arrayColl[1]#" item="k">
<cfif isNumeric(arrayColl[1][k])>
<!--- decimal or integer? --->
<cfset numericType = "integer">
<cfloop from="1" to="#arrayLen(arrayColl)#" index="i">
<cfif arrayColl[i][k] - fix(arrayColl[i][k]) gt 0>
<cfset numericType = "decimal" />
<cfbreak />
</cfif>
</cfloop>
<cfset typeList = listAppend(typeList, numericType) />
<cfelseif isSimpleValue(arrayColl[1][k])>
<cfset typeList = listAppend(typeList, 'varchar') />
<cfelseif isBoolean(arrayColl[1][k])>
<cfset typeList = listAppend(typeList, 'bit') />
<cfelseif isDate(arrayColl[1][k])>
<cfset typeList = listAppend(typeList, 'date') />
<cfelse>
<cfthrow message="Invalid ArrayCollection"
detail="All keys in your array collection must be of one of the following types: Numeric (Int or Float), String, Boolean, Date. The following key contains data that is not one of these types: `#k#`" />
</cfif>
</cfloop>
<cfset qResult = queryNew(columnList, typeList) />
<cfloop from="1" to="#arrayLen(arrayColl)#" index="i">
<cfset queryAddRow(qResult) />
<cfloop collection="#arrayColl[i]#" item="k">
<cfif not isNumeric(arrayColl[i][k]) and not isSimpleValue(arrayColl[i][k]) and not isBoolean(arrayColl[i][k]) and not isDate(arrayColl[i][k])>
<cfthrow message="Invalid ArrayCollection"
detail="All keys in your array collection must be of one of the following types: Numeric (Int or Float), String, Boolean, Date. The following key contains data that is not one of these types: `#k#`" />
</cfif>
<cfset querySetCell(qResult,k,arrayColl[i][k]) />
</cfloop>
</cfloop>
<cfreturn qResult />
</cffunction>
<cfset testData = [
{Num=2, String='fubar!', Bool=true, Date=CreateDate(2009,05,2)},
{Num=4.8, String='bufar!', Bool=false, Date=CreateDate(2009,06,1)},
{Num=6, String='futbol!', Bool=true, Date=CreateDate(2009,07,12)},
{Num=8, String='string!', Bool=false, Date=CreateDate(2009,08,9)},
{Num=10, String='data type!', Bool=true, Date=CreateDate(2009,09,18)},
{Num=12, String='yes', Bool=false, Date=CreateDate(2009,10,6)}
] />
<cfdump var="#testData#" label="data in">
<cfdump var="#arrayCollectionToQuery(testdata)#" label="data out">
Posted in ColdFusion | Flex | 
0 comments