CFLib.org – QueryToStructOfStructures

QueryToStructOfStructures(theQuery, primaryKey [, retainSort])

Last updated January 26, 2011

author

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

Rated 3 time(s). Average Rating: 5.0

Description:
Dumps an entire query into a structure of structures, with each row being easily accessible by knowing its primaryKey value. By making the passed primaryKey a foreign key from another table, you can effectively "join" tables that you couldn't join otherwise. Some code based on QueryToArrayOfStructures() by Nathan Dintenfass (nathan@changemedia.com)

Return Values:
Returns a structure.

Example:

view plain print about

<CFSET Query = QueryNew("id,name,age")>
<CFLOOP INDEX="X" FROM=1 TO=3>
	<CFSET QueryAddRow(Query,1)>
	<CFSET QuerySetCell(Query,"ID",X+10)>
	<CFSET QuerySetCell(Query,"Name","Name #X#")>
	<CFSET QuerySetCell(Query,"Age",X+15)>
</CFLOOP>

<CFSET structStruct = QueryToStructOfStructures(Query, "ID")>
<CFDUMP VAR="#structStruct#">
<br>
<cfloop index="id" from="11" to="13">
	<cfoutput>structStruct[#id#] = #structStruct[id].name#, #structStruct[id].age#<br></cfoutput>
</cfloop>

<CFSET Query = QueryNew("id,name,age")>
<CFLOOP INDEX="X" FROM=1 TO=3>
    <CFSET QueryAddRow(Query,1)>
    <CFSET QuerySetCell(Query,"ID",X+10)>
    <CFSET QuerySetCell(Query,"Name","Name #X#")>
    <CFSET QuerySetCell(Query,"Age",X+15)>
</CFLOOP>

<CFSET structStruct = QueryToStructOfStructures(Query, "ID")>
<CFDUMP VAR="#structStruct#">
<br>
<cfloop index="id" from="11" to="13">
    <cfoutput>structStruct[#id#] = #structStruct[id].name#, #structStruct[id].age#<br></cfoutput>
</cfloop>

Parameters:

Name	Description	Required
theQuery	The query you want to convert to a structure of structures.	Yes
primaryKey	Query column to use as the primary key.	Yes
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>
/**
 * Converts a query object into a structure of structures accessible by its primary key.
 * v2 mod by James Moberg - added retainSort
 * 
 * @param theQuery 	 The query you want to convert to a structure of structures. (Required)
 * @param primaryKey 	 Query column to use as the primary key. (Required)
 * @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 Returns a structure. 
 * @author Shawn Seley (&#115;&#104;&#97;&#119;&#110;&#115;&#101;&#64;&#97;&#111;&#108;&#46;&#99;&#111;&#109;) 
 * @version 2, January 26, 2011 
 */
function QueryToStructOfStructures(theQuery, primaryKey){
       var theStructure  = structnew();
       /* remove primary key from cols listing */
       var cols          = ListToArray(ListDeleteAt(theQuery.columnlist, ListFindNoCase(theQuery.columnlist, primaryKey)));
       var row           = 1;
       var thisRow       = "";
       var col           = 1;
       var retainSort = false;
       if(arrayLen(arguments) GT 2) retainSort = arguments[3];
       if(retainSort){
               theStructure = CreateObject("java", "java.util.LinkedHashMap").init();
       }
       for(row = 1; row LTE theQuery.recordcount; row = row + 1){
               thisRow = structnew();
               for(col = 1; col LTE arraylen(cols); col = col + 1){
                       thisRow[cols[col]] = theQuery[cols[col]][row];
               }
               theStructure[theQuery[primaryKey][row]] = duplicate(thisRow);
       }
       return(theStructure);
}
</cfscript>
		<cfscript>
/**
 * Converts a query object into a structure of structures accessible by its primary key.
 * v2 mod by James Moberg - added retainSort
 * 
 * @param theQuery      The query you want to convert to a structure of structures. (Required)
 * @param primaryKey      Query column to use as the primary key. (Required)
 * @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 Returns a structure. 
 * @author Shawn Seley (shawnse@aol.com) 
 * @version 2, January 26, 2011 
 */
function QueryToStructOfStructures(theQuery, primaryKey){
       var theStructure  = structnew();
       /* remove primary key from cols listing */
       var cols          = ListToArray(ListDeleteAt(theQuery.columnlist, ListFindNoCase(theQuery.columnlist, primaryKey)));
       var row           = 1;
       var thisRow       = "";
       var col           = 1;
       var retainSort = false;
       if(arrayLen(arguments) GT 2) retainSort = arguments[3];
       if(retainSort){
               theStructure = CreateObject("java", "java.util.LinkedHashMap").init();
       }
       for(row = 1; row LTE theQuery.recordcount; row = row + 1){
               thisRow = structnew();
               for(col = 1; col LTE arraylen(cols); col = col + 1){
                       thisRow[cols[col]] = theQuery[cols[col]][row];
               }
               theStructure[theQuery[primaryKey][row]] = duplicate(thisRow);
       }
       return(theStructure);
}
</cfscript>