CODEWITHSUNDEEP
javascriptjsonajaxcoldfusion
Cmaso
Cmaso

Reputation: 1113

How to use JSON returned from ajax coldfusion cfc call

Using CF 10. In order to repopulate a select without refreshing the entire page, I'm doing a remote cfc call on change of the select, which runs a query and returns it in JSON format. In my $.post() callback, I take this JSON text and immediately use JSON.parse() to convert it into a JS object. So I now have a JS object containing 2 arrays: COLUMNS, which is an array of all the column names, and DATA, which is an array of arrays, each array representing a row in the query. So each field in the query is DATA[rownumber][columnnumber].

What I'm trying to figure out is the most elegant way to handle this JS object. i can easily just iterate over it with $.each() and populate the select's options, but what if I want to sort the resultset on one or more columns? Or refer to fields by their column name? I'm wondering if I'd be better off doing something inside the cfc before returning qry, such as converting it to a struct. Below is my cfc function code:

<cffunction name="getStaffRemote" access="remote" returntype="query" returnformat="JSON" output="false">
    <cfargument name="iEmpId" type="any" required="false" />

    <cfset var qry= "" />

    <cfquery name="qry" datasource="#this.DSN#">
        SELECT iEmpid, vEmpLname, vEmpFname, isActive
        FROM tEmployees
        where 1=1
        <cfif isDefined("arguments.iEmpId") AND isNumeric(arguments.iEmpId) AND arguments.iEmpId GT 0>
            AND iEmpId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.iEmpId#" />
        </cfif>
    </cfquery>

    <cfreturn qry />

</cffunction>

Thanks for your thoughts. Incidentally, I'm also wondering why the query is accepting this.DSN, which is just a copy of application.dsn. I thought that application scope variables aren't available to a function when it's being called directly by remote, i.e., "$.post('myCFC.cfc?method=getStaffRemote&...'"

(Reply to answer):

Very nice - thanks! I'll remember this for larger queries. In this case, I ended up using the returned JSON as is with jQuery - simple to do since there are just a few columns:

$.get("cfc/employees.cfc?method=getStaffRemote&vActive=" + vActive,
                function(jsonText){
                    var qryStaff = JSON.parse(jsonText);

                    //qryStaff is now an object with 2 arrays: COLUMNS and DATA.
                    //COLUMNS is an array of strings of the column names.
                    //DATA is an array of arrays, where each array is a row.
                    //Thus, every data cell is DATA[rownumber][columnnumber]
                    //[0] = iEmpId
                    //[1] = vEmpLName
                    //[2] = vEmpFName
                    //[3] = isActive

                    $('#iEmpID_Staff').empty();
                    $.each(qryStaff.DATA, function() {
                        $('#iEmpID_Staff').append('<option value=' + $(this)[0] + '>' + $(this)[1] + ', ' + $(this)[2] + '</option>');
                    });
                }
            );

Upvotes: 1

Views: 2457

Answers (1)

John Whish
John Whish

Reputation: 3036

The JSON that ColdFusion produces when serialising a query object is not the nicest to work with. What I often do is to convert the query to an array of structs and return that. You'll end up with a larger JSON payload as column names will be repeated, however if you have gzip enabled that will help, but you'll need to consider how much data you have and whether this is a good solution in this instance but worth considering so your front end devs get JSON in a sane format :)

Something like this should do it:

<cffunction name="getStaffRemote" access="remote" returntype="query" returnformat="JSON" output="false">
    <cfargument name="iEmpId" type="any" required="false" />

    <cfset var qry= "" />

    <cfquery name="qry" datasource="#this.DSN#">
        SELECT iEmpid, vEmpLname, vEmpFname, isActive
        FROM tEmployees
        where 1=1
        <cfif StructKeyExists(arguments, "iEmpId") AND isNumeric(arguments.iEmpId) AND arguments.iEmpId GT 0>
            AND iEmpId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.iEmpId#" />
        </cfif>
    </cfquery>

    <cfscript>
        var result = [];
        for (var row in qry) {
            arrayAppend(result, row);
        }
        return result;
    </cfscript>

</cffunction>

That'll return JSON which looks something like:

[
    {"IEMPID":1, "VEMPLNAME":"Bloggs", "VEMPFNAME":"Jo", "ISACTIVE": true},
    {"IEMPID":2, "VEMPLNAME":"Smith", "VEMPFNAME":"Sam", "ISACTIVE": true}
]

Sorting in JS becomes much simpler now as you can do:

myArray.sort(function(a, b) {
    return something; // do your compare here
});

You could alternatively pass the column you want to sort in as an 2nd argument to your getStaffRemote method and add an or clause. You'll need to protect that against SQL injection attacks as well if you go that route.

Upvotes: 2

Related Questions