CFLib.org – QueryToStructOfStructures

QueryToStructOfStructures(theQuery, primaryKey)

Last updated March 27, 2002

author

Version: 1 | Requires: ColdFusion 5 | Library: DataManipulationLib

Rated 2 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:

<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

Full UDF Source:

	
	<cfscript>
/**
 * Converts a query object into a structure of structures accessible by its primary key.
 * 
 * @param theQuery      The query you want to convert to a structure of structures. 
 * @param primaryKey      Query column to use as the primary key. 
 * @return Returns a structure. 
 * @author Shawn Seley (shawnse@aol.com) 
 * @version 1, March 27, 2002 
 */
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;

  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>