Web

“You want to do what?”

Eric Jones
Share

Ok I know this isn’t part 2 of my CFC’s for the common developer but I wanted to share with you something cool I did today. I had a query of locations which needed an extra row inserted into it. Now this sounds like an easy thing to do but there’s a catch, isn’t there always a catch. I had to have the query sort the locations alphabetically but this new row which I needed to insert had to be first in line. So my task was to:

  1. Query the database and get a listing of all locations and their respective ID’s and order them alphabetically
  2. Insert into the first row of this same query a listing for Corporate Office with an ID of -99

So here’s my query:

<cfquery name="getLocations" datasource="MyDSN">
     SELECT franchiseeName + ' (' + city + ', ' + stateProvince + ')' AS lookupdisplay, franchiseeid AS lookupkey
     FROM franchisee
     ORDER BY lookupdisplay
</cfquery>

This returns a nice query result set with two columns lookupDisplay which has my location name / state and lookupKey which has the ID for this particular location. So how do I get another row into this query? Well adding a row is easy we can simple do:

<cfset QueryAddRow(getLocations,1)>

The QueryAddRow function will insert a single blank row (changing the 1 to some other number would insert that number of rows) into my result set getLocations. No in order for me to populate this row I just need to do the following:

<cfset QuerySetCell(getLocations, 'lookupDisplay',Corporate Posting (Waco, TX)')>
<cfset QuerySetCell(getLocations, 'lookupkey','-99')>

Using the QuerySetCell function I can populate my empty rows BUT there is a problem with this. The row is at the bottom of my query and I need it to be at the top. My first thought was to simply do a Query of a Queries and just reorder everything BUT my added value begins with a C and I know I have locations which start with A and B (about 8 total to be exact). So what is a developer to do? Well I start to think about things (specifically data structures) which are easy to shuffle things around in. To me arrays are the easiest data structure to sort and manipulate, but query results aren’t simple arrays. They are more like arrays with structures in each position; each row is an array positions and the columns make up the structure.

Ok so I need to write some code which will take my query and turn it into an array of structures. Easy right? Sure is thanks to www.cflib.org! If you’ve never been to CFlib you need to go there right now, it so rocks! It’s a site full of great functions for some simple, and some complex things. A quick search in the data manipulation library got me the two functions I needed ArrayOfStructuresToQuery (by David Crawford) and QueryToArrayOfStructures (by Nathan Dintenfass).

With these two functions I could turn my query into one wicked array (you should try dumping it out and take a peak for yourself!) which I could then use some code to bubble sort my last record to the top. So my next step after my query has been created and my record has been added to it is to turn my query into an array of structures like so:

<cfset getLocationsArray = QueryToArrayOfStructures(getLocations)>

Then I could do a CFLoop against this array and use the ArraySwap function to create my bubble sort like so:

<cfloop index="j" from="1" to="#ArrayLen(getLocationsArray)#">
     <cfset ArraySwap(getLocationsArray,j,ArrayLen(getLocationsArray))>
</cfloop>

Let me break down those last 3 lines for you. Line one is my CFLoop tag and in particular it’s an index loop, you programmer types will know this as a FOR loop but in ColdFusion it’s just an index loop. I set my index to j which is going to be a variable I can use inside my CFLoop tags. The from attribute is set to 1, remember in ColdFusion arrays always start with 1 while most other languages start at 0. The to attribute is actually the result of my ArrayLen() function, which is passed in the result of the QueryToArrayOfStructures functions, this ArrayLen function will return a numeric value equal to the number of array positions to ensure I don’t loop outside of my array. If I just set the to attribute to say 50 but only had 45 array positions ColdFusion would generate an ArrayIndexOutOfBounds error, basically telling me that I tired to reference something which didn’t exist.

Ok so line 2 uses the ArraySwap function to swap whatever position I’m currently at, that’s the j variable, with what ever is at the bottom of my array thereby causing whatever is at the bottom to rise up to the top. Confused? yea me too, so lets talk this through.

Ok so lets assume my array only has 5 positions in it and looks something like so:

getLocationsArray[1] = Struct A
getLocationsArray[2] = Struct B
getLocationsArray[3] = Struct C
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct E

Now the goal here is to get Struct E to be at the top and then for everything else to go A – D. So at the end of my first pass in my CFLoop my array will look like so:

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct B
getLocationsArray[3] = Struct C
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct A

Pass #2 will swap out what ever is in position 2 with whatever is in position 5

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct A
getLocationsArray[3] = Struct C
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct B

Pass #3 will swap out position 3 with positions 5

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct A
getLocationsArray[3] = Struct B
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct C

Pass #4 will swap out position 4 with position 5

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct A
getLocationsArray[3] = Struct B
getLocationsArray[4] = Struct C
getLocationsArray[5] = Struct D

Now at this point it’s good but our loop as it’s coded now will do another swap between position 5 and position 5 which results in nothing really happening.

So now you can see that I have exactly what I needed. The great thing about this is if I needed it to appear at position 3 all I have to do is change the from value in my CFLoop to 3 and the same thing will happen but it will work from 3 to 5.

Ok now that everything is the way I want it I just need to turn it back into a query result set using:

<cfset getLocations = ArrayOfStructuresToQuery(getLocationsArray)>

Using the ArrayOfStructuresToQuery function takes me back to my query which now has the data in the exact order I wanted it.

Not to shabby huh? How would you have approached the problem?