CFLib.org – queryColumnsToStruct

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

Last updated January 24, 2011

author

Version: 2 | Requires: ColdFusion MX | Library: DataManipulationLib

Rated 7 time(s). Average Rating: 4.6

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:

view plain print about

<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)#">

<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 
<cfdump var="#q#">
 
USING ONLY THE ID COLUMN: 
<cfdump var="#queryColumnsToStruct(q,"id")#">
 
USING "ID" AS THE KEY, AND "NAME" AS THE VALUE: 
<cfdump var="#queryColumnsToStruct(q,"id","name")#">
 
USING "GROUP" AS THE KEY, AND "NAME" AS THE VALUE: 
<cfdump var="#queryColumnsToStruct(q,"group","name")#">
 
SAME AS ABOVE, BUT RUN IN REVERSE (notice a different "marines" value): 
<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:

	
	
			
			
			
				
				view plain
				
				
				copy to clipboard
				
				
				
				
				print
				
				
				about
				ColdFISH is developed by Jason Delmore.  Source code and license information available at coldfish.riaforge.org.  Version 3.1.1
				
			<cfscript>
/**
 * 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 (&#110;&#97;&#116;&#104;&#97;&#110;&#64;&#99;&#104;&#97;&#110;&#103;&#101;&#109;&#101;&#100;&#105;&#97;&#46;&#99;&#111;&#109;) 
 * @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;
}
</cfscript>
		<cfscript>
/**
 * 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;
}
</cfscript>