CFLib.org – Common Function Library Project

queryColumnsToStruct(query, keyColumn[, valueColumn][, reverse][, retainSort])

Last updated January 24, 2011

author

Nathan Dintenfass

Version: 2 | Requires: CF6 | Library: DataManipulationLib

Description:
Creates a structure keyed off one column in a given query. You can choose to either have the value be the same as the key or choose another column for the value. Since this function uses only simple values as the values in the structure duplicate keys will be overwritten. You can control this to some extent with the optional 4th argument, which you set to "true" if you want to go through the query in reverse order (which would result in the top-most value of a given key being used as opposed to the bottom-most value). Typically, you'll use the primary key of the table in the query as the key in the struct, so it should not be an issue in most cases.

Return Values:
struct

Example:

<cfscript>
	q = queryNew("id,name,group");
	queryAddRow(q,4);
	querySetCell(q,"id","a",1);
	querySetCell(q,"id","b",2);
	querySetCell(q,"id","c",3);
	querySetCell(q,"id","d",4);
	querySetCell(q,"name","Raymond",1);
	querySetCell(q,"name","Ben",2);
	querySetCell(q,"name","Joel",3);
	querySetCell(q,"name","Scott",4);
	querySetCell(q,"group","marines",1);
	querySetCell(q,"group","navy",2);
	querySetCell(q,"group","airforce",3);
	querySetCell(q,"group","marines",4);	
</cfscript>

HERE'S THE QUERY<br />
<cfdump var="#q#">
<br />
USING ONLY THE ID COLUMN:<br />
<cfdump var="#queryColumnsToStruct(q,"id")#">
<br />
USING "ID" AS THE KEY, AND "NAME" AS THE VALUE:<br />
<cfdump var="#queryColumnsToStruct(q,"id","name")#">
<br />
USING "GROUP" AS THE KEY, AND "NAME" AS THE VALUE:<br />
<cfdump var="#queryColumnsToStruct(q,"group","name")#">
<br />
SAME AS ABOVE, BUT RUN IN REVERSE (notice a different "marines" value):<br />
<cfdump var="#queryColumnsToStruct(q,"group","name",true)#">

Parameters:

Name Description Required
query The query to operate on Yes
keyColumn The name of the column to use for the key in the struct Yes
valueColumn The name of the column in the query to use for the values in the struct (defaults to whatever the keyColumn is) No
reverse Boolean value for whether to go through the query in reverse (default is false) No
retainSort If true, a Java LinkedHashMap will be used to create the result. This will create a struct with ordered keys. Defaults to false. No

Full UDF Source:

/**
 * Makes a struct for all values in a given column(s) of a query.
 * v2 by James Moberg
 * 
 * @param query 	 The query to operate on (Required)
 * @param keyColumn 	 The name of the column to use for the key in the struct (Required)
 * @param valueColumn 	 The name of the column in the query to use for the values in the struct (defaults to whatever the keyColumn is) (Optional)
 * @param reverse 	 Boolean value for whether to go through the query in reverse (default is false) (Optional)
 * @param retainSort 	 If true, a Java LinkedHashMap will be used to create the result. This will create a struct with ordered keys. Defaults to false. (Optional)
 * @return struct 
 * @author Nathan Dintenfass (nathan@changemedia.com) 
 * @version 2, January 24, 2011 
 */
function queryColumnsToStruct(query,keyColumn){
       var valueColumn = keyColumn;
       var reverse = false;
       var retainSort = false;
       var struct = structNew();
       var increment = 1;
       var ii = 1;
       var rowsGotten = 0;
       if(arrayLen(arguments) GT 2) valueColumn = arguments[3];
       if(arrayLen(arguments) GT 3) reverse = arguments[4];
       if(arrayLen(arguments) GT 4) retainSort = arguments[5];
       if(retainSort){
               struct = CreateObject("java", "java.util.LinkedHashMap").init();
       }
       if(reverse){
               ii = query.recordCount;
               increment = -1;
       }
       while(rowsGotten LT query.recordCount){
               struct[query[keyColumn][ii]] = query[valueColumn][ii];
               ii = ii + increment;
               rowsGotten = rowsGotten + 1;
       }
       return struct;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson