http://coldfusion.com logo
Join Slack
Powered by
I am using the function QueryToArray ```<cffun...
# lucee
g
I am using the function QueryToArray
Copy code
<cffunction name="QueryToArray" access="public" returntype="array" output="false" hint="This turns a query into an array of structures.">
        <cfargument name="Data" type="query" required="yes" />
        <cfscript>
            var LOCAL = StructNew();
            LOCAL.Columns = ListToArray( queryColumnList(arguments.data) );
            LOCAL.QueryArray = ArrayNew( 1 );
            for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){
                LOCAL.Row = StructNew();
                    for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){
                        LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];
                        LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];
                    }
                ArrayAppend( LOCAL.QueryArray, LOCAL.Row );
            }
            return( LOCAL.QueryArray );
        </cfscript>
    </cffunction>
but it is still returning me the sorted list, is there any other function or something which returns me the array of objects as they were in the query
s
2 simple options based on your situation. 1. just set the returnType=‘array’ in your actual query to the database and you will get an array of structs. 2. run a Query-Of-Queries on the returned query and set the returnType=‘array’
👍 2
g
ok, i tried your solution and it works in cases but my code is written is such a manner that it uses ColumnList and that brings the sort in the coldfusion way, now i wrote another function to sort it according to my need as:
Copy code
<cffunction name="sortedData">
        <cfargument name="data" required="true" type="any">
        <cfset ddata = DeserializeJSON(arguments.data)>
        <cfset cquery = QueryNew("id,gametype,gamedate,hometeam,awayteam,location,gametime,gamenumber,notes", "Integer,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar")>
        <!--- Loop through the JSON array and add rows to the ColdFusion query --->
        <cfloop array="#ddata#" index="item">
          <cfset QueryAddRow(cquery)>
          <cfset QuerySetCell(cquery, "id", item.id)>
          <cfset QuerySetCell(cquery, "gametype", item.gameType)>
          <cfset QuerySetCell(cquery, "gamedate", item.gameDate)>
          <cfset QuerySetCell(cquery, "hometeam", item.homeTeam)>
          <cfset QuerySetCell(cquery, "awayteam", item.awayTeam)>
          <cfset QuerySetCell(cquery, "location", item.location)>
          <cfset QuerySetCell(cquery, "gametime", item.gameTime)>
          <cfset QuerySetCell(cquery, "gamenumber", item.gameNumber)>
          <cfset QuerySetCell(cquery, "notes", item.notes)>
        </cfloop>

        <!--- Sort the ColdFusion query --->
        <cfset QuerySort(cquery, "id,gametype,gamedate,hometeam,awayteam,location,gametime,gamenumber,notes")>

        <!--- Convert the sorted ColdFusion query back to JSON --->
        <cfset sortedJson = SerializeJSON(cquery,'struct')>
        <cfreturn sortedJson>
    </cffunction>
the above functions sorts it properly, but now the problem is whenever i edit a column, it shifts to the end not sure if its related to this or something else but its a problem now
m
maybe i'm just silly, but i don't know if I really understand what you are sending and what you are expecting. My interpretation is you are asking to have the struct columns ordered in the same order as the query results columns. If so, maybe on your first version, change your LOCAL.Row = StructNew(); to LOCAL.Row = StructNew("linked"); or LOCAL.Row = [:]; would likely accomplish it, because structs aren't ordered by default. also, that code looked really old. var LOCAL = StructNew(); i'd just remove that line altogether, local should already exist there. If i look at the later one, and compare the docs, I would recommend making the second attribute of querySort just the column you want sorted on, not all of them, my guess is that is why it's inconsistent. I'd also probably just try <cfset queryAddRow(cquery, ddata)> instead of the loop and sets.
g
i tried your solution, but it seems the moment i do a resort of the query columns and return it serialized, the code messes up, it works if i do not sort
s
Can you turn this into a CF try example so we can actually look at it
g
i will try
ok, its to much copde and imposible to do a trycf gist, but i noticed that when i use this function, it returns me the aray of strures in an sortedOrder, i want to be returned in the same order the way query is sending it to it using the queryColumnList <cffunction name="QueryToArray" access="public" returntype="array" output="true" hint="This turns a query into an array of structures."> <cfargument name="Data" type="query" required="yes" /> <cfscript> var LOCAL = [:]; LOCAL.Columns = ListToArray( QueryColumnList(arguments.data) ); LOCAL.QueryArray = ArrayNew( 1 ); for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){ LOCAL.Row = StructNew(); for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){ LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ]; LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ]; } ArrayAppend( LOCAL.QueryArray, LOCAL.Row ); } dump(local.QueryArray); return( LOCAL.QueryArray ); </cfscript> </cffunction>
Copy code
this fixed it 
<cffunction name="QueryToArray" access="public" returntype="array" output="true" hint="This turns a query into an array of structures.">
        <cfargument name="Data" type="query" required="yes" />
        <cfscript>
            var LOCAL = [:];
            //dump(QueryColumnList(arguments.data));
            LOCAL.Columns = listToArray(arguments.data.getColumnList(false));
            //dump(LOCAL.Columns);
            LOCAL.QueryArray = ArrayNew( 1 );
            for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){
                LOCAL.Row = [:];
                    for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){
                        LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];
                        LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];
                    }
                ArrayAppend( LOCAL.QueryArray, LOCAL.Row );
            }
            //dump(local.QueryArray);
            return( LOCAL.QueryArray );
        </cfscript>
    </cffunction>
m
you should still remove var LOCAL = [:]; / lucee is already creating local, that line has no value, except to makes it more confusing.
2 Views
Open in Slack
PreviousNext
Powered by