trying to get the data like this from the cfc function

Question

I have a cfc where i need t return the headers for the jqgrid for binding dynamically

i am trying to query the data like this:

colNames: ['ID', 'Institution Name', 'Display Name', 'Short Name', 'Board of Education', 'Scheme Name','Subscription Date'], 
colModel: [ 
{ name: 'institutionid', sortable: true,  },
{ name: 'institutionname', sortable: true },
{ name: 'displayname', sortable: true },
{ name: 'shortname' ,sortable: true},
{ name: 'supportedfield', sortable: true },
{ name: 'schemename', sortable: true },
{ name: 'subscriptionto', sortable: true}

]

i can easily get the colNames, but for colmodal how can i bring the elements of sort: true for all by default and format should be like arrayofstructs

Thanks

Query try

<cffunction name="headers" localmode="modern" access="remote" returnformat="json" hint="Handles the Functionality of returning the Table Headers">


        <cfset columnsInfos = {}>
        <cfset returnArray = []>

        <cfset cList = QueryExecute("select top 1 * from mytable").columnList>
        <cfset cListQueryObj = QueryNew(cList)>
        <cfdump var="#cListQueryObj#" abort>
        <cfset colNames = ListtoArray(cList)>
        <cfloop query="#cListQueryObj#">
        <cfset rowStruct = {}>
          <cfloop list="#cList#" index="colname">
              <cfset "rowStruct['#colname#']" = cListQueryObj[colname]>
          </cfloop>
          <cfset arrayAppend(returnArray,rowStruct)>
          <cfdump var="#rowStruct#">
        </cfloop>

        <cfset columnsInfos["colModel"] = returnArray>
        <cfset columnsInfos["colNames"] = colNames>
        <cfreturn columnsInfos>
    </cffunction>

Answer 1

Serializing a query object won't return the expected result. Since you are using the built-in returnFormat="json", the easiest (and probably only) approach is working with an array of structs, just like your JS example shows:

<cffunction name="headers" localmode="modern" access="public" returnformat="json" hint="Handles the Functionality of returning the Table Headers">

    <!--- best practise: declare the returned scheme --->
    <cfset result = {
        "colNames": [],
        "colModel": []
    }>

    <!--- get your table's columns (mockup) --->
    <cfset columnList = "institutionid,institutionname,displayname,shortname,supportedfield,schemename,subscriptionto">

    <!--- use the column names from your query? --->
    <cfset result["colNames"] = listToArray(columnList)>

    <!--- add an entry with "name" and "sortable" for every column --->
    <cfloop list="#columnList#" index="columnName">

        <cfset result["colModel"].add({
            "name":     columnName,
            "sortable": true
        })>

    </cfloop>

    <cfreturn result>
</cffunction>

Note that we are not calling serializeJSON on the result, because returnFormat="json" will already do that for us. Regarding your colNames: Your JS example uses mapped column names, not dynamic ones as shown in your code and the code above. You might want to make them either static or map them yourself.

Answer 2

I like Alex's approach, but I like cfscript better. I also like localized variables.

<cfscript>
/**
 * @hint         Handles the Functionality of returning the Table Headers
 * @output       false
 * @returnFormat JSON
*/ 
public any function headers() {

  // best practise: declare the returned scheme
  var result = {
      "colNames": [],
      "colModel": []
  };

  // get your table's columns (mockup) 
  var columnList = "institutionid,institutionname,displayname,shortname,supportedfield,schemename,subscriptionto";

  // use the column names from your query? 
  result.colNames = listToArray(columnList);

  // add an entry with "name" and "sortable" for every column 
  for (var columnName in columnList)  {

     result.colModel.add({
        "name":     columnName,
        "sortable": true
    });
  }

  return result;
}

writedump(headers);
writedump(headers());
</cfscript>

Function signature

Function results

Also see JSON response using cfscript function