How to reorder struct columns?

Question

Hey guys,

I'm trying to display my results from a CFQuery in a specific order. The order is to be maintained in the database so that it can be manipulated, and there are an unknown number of columns per table. The final row in the table is "ColumnOrder": each column has a number to specify it's sort order, 0 means "don't display". I'm trying to sort by looping say, "y" from 1 to maxCols:

0) do y = 1 to maxCols

1) in the sortColumn result set, use y to lookup the corresponding KEY

2) in the products result set, find the value from the corresponding KEY

3) insert said value into tempStruct[y]

4) loop.

I'm running into a wall trying to use structFindKey(). Here's my code:

<CFQUERY name="qParts" datasource="Pascal">         
    SELECT * FROM Turbos WHERE PartNumber LIKE <cfqueryparam cfsqltype="cf_sql_char" maxlength="30" value="%#mfr#%"> ORDER BY #sort# ASC 
</CFQUERY>
<cfquery name="qPartsOrder" datasource="Pascal">
    SELECT * FROM Turbos WHERE PartNumber = 'ColumnOrder'
</cfquery>

<cfset tempStruct=structnew()>
        <cfloop index="columnOrder2" from="1" to="#ListLen(qPartsOrder.ColumnList, ',')#">
            <cfdump var="#StructFindKey(qPartsOrder, columnOrder2)#">
            <cfset tempStruct[columnOrder2] = StructFindKey(#qPartsOrder#, "#columnOrder2#")>
            <cfset currentCol = "#ListGetAt(qParts.columnList, columnOrder2, ',')#">
            <cfoutput>#qParts[currentCol][qParts.currentrow]# <br/></cfoutput>
        </cfloop>
<cfdump var="#tempstruct#">

The line

<cfdump var="#StructFindKey(qPartsOrder, columnOrder2)#">

is throwing a BLANK!! error message, so I can't debug it and I'm stuck.

Any and all help would be appreciated (and YES I have to use SELECT *, this is a generic product display page for displaying ALL information in the database except a few which are denoted by a zero in the order column, remember?).

Answer 1

I'm not 100% sure that I understand the problem you are trying to solve. The is exacerbated by a very unconventional way of setting up a database.

To begin with, if you are not lucky you may run into a documented error where using a cfqueryparam tag throws an error of Value cannot be converted to requested type although I don't know if this still happens with current versions of ColdFusion (8+).

In any case, you can always select all of the columns of the table manually even if you don't know how many of them will ultimately be used:

SELECT partNumber, secondColumn, thirdColumn, ... , nthColumn
FROM Turbos

This is generally preferable to just using SELECT * although it presents some problems if you are in the habit of frequently adding/removing columns to tables.

Unless you need to use a Struct for good reason, you should use an Array instead. Structs don't store ordering information while Arrays do. Here is one way to sort through the records in qParts:

<cfset RecordsArray=ArrayNew(2)>
<cfset ColumnIndex=StructNew()>
<cfloop list="#qPartsOrder.ColumnList#" index="order_column">
    <cfset ColumnIndex[order_column]=val(qPartsOrder[order_column][1])>
</cfloop>
<cfloop query="qParts">
    <cfloop list="#qPartsOrder.ColumnList#" index="order_column">
        <cfif val(ColumnIndex[order_column])>
            <cfset RecordsArray[ColumnIndex[order_column]][qParts.CurrentRow]=qParts[order_column][qParts.CurrentRow]>
        </cfif>
    </cfloop>
</cfloop>

The result of this code will be a 2D array, with the first number referring to the column index and the second index pointing to the record row.

All in all, I think that unless you have zero control over how the database is structured, there is a better way to implement this, starting with how you've set up your database. It would really help to see some fake sample data as well as having a clearer idea of what you are trying to accomplish -- what will you do with these ordered fields once you have them, for example?

Answer 2

Dun you try to use StructSort ?