ColdFusion in Context: Matrix Manipulation

Suppose you need to manipulate a matrix in memory. You could create it as a simulated query, but then you wouldn't be able to re-sort it. You could store each column in a separate array, list, or structure, but then sorting a column would cause it to lose its relationship with the other columns (as if you had selected a single spreadsheet column and told the spreadsheet engine to sort the selection). Here's one method that will let you get your hands around the problem: store the data as an array of lists where each array element is a row and the list elements are columns in that row. Let the element(s) on which the sort depends occur first in the list, and simply sort the array as if its lists were strings (which they are).

One problem you'll encounter in getting data from a database table into this configuration is that the query may not fully populate every column in every row. Some fields in the table may be empty. ColdFusion (and other languages) will try not to append empty elements to a list, and when reading data from a list that has gaps, will skip an empty element, reading data from the NEXT element as if it had come from the empty column. This has the logical impact of mixing data across fields and will also cause an error when it becomes time to read the last element in the row. To avoid this, you need to populate every column and then ignore the placeholder you use for this purpose when it comes time to display the data stored there.

Begin with Data

Create a table named Family that will be joined with itself to solve a practical problem. Some families help others (HELPS=1); some need visits (NEEDS=1); most fall in both categories. The numeric equivalent of their X and Y map coordinates (to be used in computing distance between families) are XNr and YNr. Call this table Family. Give it at least these columns: 

FAMILY,HELPS,NEEDS,XNr,YNr
Fenwick,0,1,13,19
Roberts,0,1,13,21
Quales,1,1,14,18
Vance,0,1,15,19
Crowley,0,1,16,15
Dobbs,1,1,16,21
Jameson,0,1,18,10
Mack,1,0,18,18
Lubbock,0,1,18,19
Eggbert,0,1,18,21
Hicks,1,1,19,18
Thompson,1,1,19,18
Stanford,0,1,19,20
Jones,1,1,20,20

Perform the Join

Place all code in matrix.cfm. To join a table with itself, use an alias. The following query uses alias "a" to refer to families in their helper role and "b" to refer to families in the role of receiving a visit. The Distance construct uses the Pythagorean theorem to determine the distance between the helper and the family being visited. (Subtract the square of the difference of the X coordinates from the square of the distance between the Y coordinates and then take the square root of the result.) Each row will wind up containing the following fields: Helper, its X and Y coordinates (HelperX, HelperY), Family (receiving a visit), its X and Y coordinates (FamilyX, FamilyY), and the Distance between them. 

<!--- Determine the distance between each useful pair
and sequence by shortest distance first --->
<cfquery name="tryAll" datasource="context">
select a.family as Helper,
a.XNr as HelperX, a.YNr as HelperY,
b.Family as Family,
b.XNr as FamilyX, b.YNr as FamilyY,
((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5 as Distance
from Family a, Family b
where a.helps = TRUE
and b.needs = TRUE
and a.Family <> b.Family
and ((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5 <> NULL
order by ((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5
</cfquery>

Store the data

For a large set of data, writing each distance back to a database in order to manipulate the data on disk would be slower than manipulating it in memory. Load the data into a matrix.

The backbone of this matrix is an array. For each row in the query, append every element in the row to a list inside the current element of the array. In case you want to use the Distance later as a string for sorting purposes, format the number with leading zeroes. Because some of the values may have been empty in the database, tack an extra character onto every element as you append it to the list. The reason the tilde is chosen here is that omitting it from data should not cause a hardship and its influence on manipulation is minimized because it falls after other characters in sort sequence. 

<!--- Load the matrix --->
<cfset Matrix=arrayNew(1)>
<cfloop query="tryAll">
<cfset TempList="">
<cfset TempList=listAppend(TempList,"#Helper#~",";")>
<cfset TempList=listAppend(TempList,"#HelperX#~",";")>
<cfset TempList=listAppend(TempList,"#HelperY#~",";")>
<cfset TempList=listAppend(TempList,"#Family#~",";")>
<cfset TempList=listAppend(TempList,"#FamilyX#~",";")>
<cfset TempList=listAppend(TempList,"#FamilyY#~",";")>
<cfset TempList=
listAppend(TempList,"#numberFormat(Distance,0000)#~",";")>
<cfset Matrix[currentrow]=TempList>
<cfoutput>#Matrix[currentrow]#<br></cfoutput>
</cfloop>

Display the Matrix

To demonstrate that the data has been correctly stored, create a simple slash-delimited display. The replace function works readily to remove the tildes. 

<!--- Simply display the matrix --->
<cfloop from=1 to="#arrayLen(Matrix)#" index="Row">
<cfoutput>
#replace(listGetAt(Matrix[Row],1,";"),"~","")#/
#replace(listGetAt(Matrix[Row],2,";"),"~","")#/
#replace(listGetAt(Matrix[Row],3,";"),"~","")#/
#replace(listGetAt(Matrix[Row],4,";"),"~","")#/
#replace(listGetAt(Matrix[Row],5,";"),"~","")#/
#replace(listGetAt(Matrix[Row],6,";"),"~","")#/
#replace(listGetAt(Matrix[Row],7,";"),"~","")#/
</cfoutput><br>
</cfloop>
<p>

Manipulate the Matrix

To demonstrate that the matrix can be manipulated without losing its integrity, sort it. In this example, the sequence of columns lends itself to a useful sort. (If desired, you could also rearrange columns, introduce new ones, and remove columns prior to sorting.)

Many similar functions return a changed object at the left side of the assignment. However, the arraySort function merely returns "Yes" (if the sort is successful, not a sorted array). It sorts the array in place. Therefore, when using this function, a dummy assignment (or no assignment at all) is appropriate. The "textnocase" parameter causes the case of text to be ignored in the sort. Bear in mind that the entire string is being sorted: all columns. 

<!--- Sort the matrix --->
<cfset Dummy=ArraySort(Matrix,"textnocase","asc")>

Display it Again

For proof that the sort worked properly, copy the preceding display code and use it again: 

<!--- Simply display the matrix --->
<cfloop from=1 to="#arrayLen(Matrix)#" index="Row">
<cfoutput>
#replace(listGetAt(Matrix[Row],1,";"),"~","")#/
#replace(listGetAt(Matrix[Row],2,";"),"~","")#/
#replace(listGetAt(Matrix[Row],3,";"),"~","")#/
#replace(listGetAt(Matrix[Row],4,";"),"~","")#/
#replace(listGetAt(Matrix[Row],5,";"),"~","")#/
#replace(listGetAt(Matrix[Row],6,";"),"~","")#/
#replace(listGetAt(Matrix[Row],7,";"),"~","")#/
</cfoutput><br>
</cfloop>
<p>

Discussion

Browse matrix.cfm. Remove some of the data from the table to prove that the code won't crash when data is missing. This technique gives you some of the advantages of database manipulation while letting you work entirely in memory. =Marty=