As I mentioned in an Intro to jqGrid, sometimes you have to deal with remote data that isn't in the 'standard' JSON recordset format. I also like to reuse my server-side code, and prefer not to unnecessarily hack native data to meet a client-side need. For this reason, it is often necessary to write client-side methods that parse the server-side native format. Unfortunately, many JQuery plugins only handle the 'standard' JSON recordset format:

'Standard' JSON Recordset

view plain print about
1{
2 data: [
3     {
4     id: "FF318BAB-3048-71C2-17E1634637074ECF",
5 title: "The ColdFusion 8 AJAX Components Debate",
6 posted: "June, 05 2007 23:51:00",
7 views: 7667
8 },
9 {
10     id: "FD48C350-3048-71C2-17244BE88532DEC9",
11            title: "ColdFusion (7 or 8) With Apache Pt 2: Multi-Instance Configuration",
12            posted: "June, 08 2007 15:06:00",
13            views: 0
14 }
15 ]
16}

It's not uncommon to get recordsets in differing formats though. This is one of the reasons we're using jqGrid, because it provides function for using something different. Using ColdFusion for our backend, the JSON serialization of a native query object returns a slimmer, trimmer format for a recordset:

JSON Serialized ColdFusion Query Object

view plain print about
1{
2 "COLUMNS": [
3 "ID",
4 "TITLE",
5 "POSTED",
6 "VIEWS"
7 ],
8 "DATA": [
9 [
10 "FF318BAB-3048-71C2-17E1634637074ECF",
11 "The ColdFusion 8 AJAX Components Debate",
12 "June, 05 2007 23:51:00",
13 7667
14 ],
15 [
16 "FD48C350-3048-71C2-17244BE88532DEC9",
17 "ColdFusion (7 or 8) With Apache Pt 2: Multi-Instance Configuration",
18 "June, 08 2007 15:06:00",
19 0
20 ],
21 ...
22 ]
23}

I know, that example doesn't look much trimmer. But when you're returning 50+ records at a time, having those column names only listed once really does cut down on the bit traffic. The bottom line, though, is that ColdFusion's format for recordsets is different. You may see similar differences in calls to restful webservices. Because of these differences, the standard jqGrid json or jsonstring datatypes will not be sufficient, so a custom datatype function is in order.

Building a function datatype is really very easy. What you're telling jqGrid is that you want to manually handle the ajax request and result mapping. You may recall, in our last post, that we created the function stub for our datatype function:

view plain print about
1/*
2 * FUNCTION populateGrid
3 * Used as the 'datatype' attribute of the jqGrid config object, this method
4 * is used to handle the ajax calls and data manipulation needed to populate
5 * data within our jqGrid instance.
6 * @postdata (object) - this is the object passed as the 'postData' attribute
7 *                         of our jqGrid instance.
8 */

9var populateGrid = function (postdata) {
10    // request and parsing code will go here
11};

The meat of our actions will occur inside this method. jqGrid will automatically pass it's postData option to this method:

From the jqGridDemo configuration

view plain print about
1grid.jqGrid({
2    ...
3 postData: {method: "GetEntries", returnFormat: "JSON"},
4 datatype: populateGrid,
5 ...
6});

In our demo code, we've created a default postData object, defining the name of our remote method as getEntries(), and asking that the return value be formatted as JSON. This object is what is typically passed as the data configuration of a JQuery ajax request, and are required paramaters when making a remote call to a ColdFusion cfc. (If returnFormat is not passed, and is not specified in the ColdFusion function's description meta, then the return value will be formatted as WDDX by default.) Before we can write our remote call though, we need a remote method to which we can make our data request.

First, let's create a basic component for our Blog (this demo is written to work against BlogCFC).

Entries.cfc

view plain print about
1/**
2 * @name Entries
3 * @displayName Blog Entries
4 * @output false
5 */

6component {
7
8    /*
9     *    Creating some constants, for validity checking
10     */

11    VARIABLES._COLUMNARRAY = ["id","title","posted","views"];
12    VARIABLES._DIRARRAY = ["asc","desc"];
13
14}

Nothing special here. Just a basic component. I have included some constants that we can use to verify that some passed data is actual valid in our calls, but we still need our function.

GetEntries Method

view plain print about
1/**
2 *    FUNCTION GetEntries
3 *    A function to get paging query of blog entries for layout in jqGrid
4 *
5 *    @access remote
6 *    @returnType struct
7 *    @output false
8 */

9function GetEntries(numeric pageIndex = 1, numeric pageSize = 50, string sortCol = "ID", string sortDir = "desc") {
10    LOCAL.retVal = {"success" = true, "pageIndex" = ARGUMENTS.pageIndex, "pageCount" = 0, "recordCount" = 0, "message" = "", "data" = ""};
11
12    return LOCAL.retVal;
13}

Basic function structure. Setup for remote access, with a returnType of 'struct'. Our arguments are defined and defaulted, with none of them as 'required'. We also create our default return value (retVal) variable. The meat of our process will adjust this variable prior to it's return in the closing line, but this defaults all of the keys, so that we only change what we need to in process.

So, the first thing I want to do is do any server-side validation that might be needed. We're going to dynamically build our query's ORDER BY clause. You can't queryparam the ORDER BY, so this is something you want to validate prior, to avoid potential SQL injection type issues.

GetEntries Method - Argument Validation

view plain print about
1if(ArrayFindNoCase(VARIABLES._COLUMNARRAY, ARGUMENTS.sortCol) AND ArrayFindNoCase(VARIABLES._DIRARRAY, ARGUMENTS.sortDir)){
2    LOCAL.orderby = ARGUMENTS.sortCol & " " & ARGUMENTS.sortDir;
3} else {
4    StructAppend(LOCAL.retVal,{"success" = false, "message" = "Your sort criteria is not valid."},true);
5    return LOCAL.retVal;
6}

This says, if both of these are valid then build our ORDER BY string, otherwise return an error to the application.

Our next step is to put our query together. Remember that we're writing for paging datasets. I'm using MySQL on the backend, so my paging query looks like this

GetEntries Method - Data Query

view plain print about
1LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS id,
2                title,
3                posted,
4                views
5            FROM    tblblogentries
6            ORDER BY #LOCAL.orderby#
7             LIMIT    :start,:numRec";
8LOCAL.q = new Query(sql = LOCAL.sql);
9LOCAL.q.addParam(name = "start", value = (ARGUMENTS.pageIndex-1) * ARGUMENTS.pageSize, cfsqltype = "cf_sql_integer");
10LOCAL.q.addParam(name = "numRec", value = ARGUMENTS.pageSize, cfsqltype = "cf_sql_integer");

My app includes the datasource name, but you can add it in the new Query() statement, if it's needed. You can see the ORDER BY statement we built a moment ago, as well as our queryparams. Our start param is a computed value, because MySQL's LIMIT statement requires the number of the record itself (i.e.: 0,50 or 50,50 or 100,50 and so on). The addParam() method is the scripted way of doing .

But we haven't run the query yet, but only set it up. Let's run the query, do some validation, and get some more data if we need it.

GetEntries Method - Get The Data

view plain print about
1try {
2    LOCAL.retVal.data = LOCAL.q.execute().getResult();
3    if(LOCAL.retVal.data.recordCount){
4        /*
5         * The next statement is used to provide a TotalCount of all matched records.
6         */

7        LOCAL.q.setSql("SELECT FOUND_ROWS() as totalCount");
8        LOCAL.totResult = LOCAL.q.execute().getResult();
9        if(LOCAL.totResult.recordCount){
10            LOCAL.retVal.recordCount = LOCAL.totResult.totalCount; // total number of records
11            LOCAL.retVal.pageCount = Ceiling(LOCAL.totResult.TotalCount / ARGUMENTS.pageSize); // total number of pages by pageSize
12        }
13    }
14} catch (any excpt) {
15    LOCAL.retVal.success = false;
16    LOCAL.retVal.message = excpt.message;
17}

We execute the query, applying the result to our return data value. If records are returned (and we hope they are), then we run another query to find out just how many records there are in all so we can set our total recordCount and pageCount variables. If no records were returned, then the return value defaults will work. The complete method looks like this:

Complete GetEntries

view plain print about
1/**
2 *    FUNCTION GetEntries
3 *    A function to get paging query of blog entries for layout in jqGrid
4 *
5 *    @access remote
6 *    @returnType struct
7 *    @output false
8 */

9function GetEntries(numeric pageIndex = 1, numeric pageSize = 50, string sortCol = "ID", string sortDir = "desc") {
10    LOCAL.retVal = {"success" = true, "pageIndex" = ARGUMENTS.pageIndex, "pageCount" = 0, "recordCount" = 0, "message" = "", "data" = ""};
11
12    if(ArrayFindNoCase(VARIABLES._COLUMNARRAY, ARGUMENTS.sortCol) AND ArrayFindNoCase(VARIABLES._DIRARRAY, ARGUMENTS.sortDir)){
13        LOCAL.orderby = ARGUMENTS.sortCol & " " & ARGUMENTS.sortDir;
14    } else {
15        StructAppend(LOCAL.retVal,{"success" = false, "message" = "Your sort criteria is not valid."},true);
16        return LOCAL.retVal;
17    }
18
19    LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS id,
20                    title,
21                    posted,
22                    views
23                FROM    tblblogentries
24                ORDER BY #LOCAL.orderby#
25                 LIMIT    :start,:numRec";
26    LOCAL.q = new Query(sql = LOCAL.sql);
27    LOCAL.q.addParam(name = "start", value = (ARGUMENTS.pageIndex-1) * ARGUMENTS.pageSize, cfsqltype = "cf_sql_integer");
28    LOCAL.q.addParam(name = "numRec", value = ARGUMENTS.pageSize, cfsqltype = "cf_sql_integer");
29
30    try {
31        LOCAL.retVal.data = LOCAL.q.execute().getResult();
32        if(LOCAL.retVal.data.recordCount){
33            LOCAL.q.setSql("SELECT FOUND_ROWS() as totalCount");
34            LOCAL.totResult = LOCAL.q.execute().getResult();
35            if(LOCAL.totResult.recordCount){
36                LOCAL.retVal.recordCount = LOCAL.totResult.totalCount; // total number of records
37                LOCAL.retVal.pageCount = Ceiling(LOCAL.totResult.TotalCount / ARGUMENTS.pageSize); // total number of pages by pageSize
38            }
39        }
40    } catch (any excpt) {
41        LOCAL.retVal.success = false;
42        LOCAL.retVal.message = excpt.message;
43    }
44    return LOCAL.retVal;
45}

If you run a quick test harness on this, even without arguments, you should now get a structure back with records (though it does require BlogCFC and some data for the demo). Now that we have our remote method we need to fill out our datatype function in our script. Let's begin with the base ajax call:

jqGridDemo.js populateGrid Method

view plain print about
1var populateGrid = function (postdata) {
2    $.ajax({
3        url: '/com/cc/Blog/Entries.cfc',
4        data:postdata,
5        method:'POST',
6        dataType:"json",
7        success: function(d,r,o){
8            
9        }
10    });
11};

Here we define the basic request, identifying our endpoint (the Entries.cfc we've been working on), passing the jqGrid postData as our data params, and saying that our return value is expected to be JSON. If you loaded the demo page right now you wouldn't see anything, unless you looked at a JavaScript console. If you were looking at a console, you would see the remote request being made, where you could inspect the post parameters (jqGrid's postData, passed into the populateGrid() method as the postdata argument) and the returned JSON. Here is where we begin to process the data into jqGrid. Let's look at our success function. First, we included a success flag in our return structure. Let's key off of that:

jqGridDemo.js - populateGrid Ajax 'success' Method

view plain print about
1success: function(d,r,o){
2    if(d.success){
3        // On 'success' do this
4    } else {
5        // If not, then do this
6    }
7}

You'll remember that we created a gridCols object as a global variable. Let's loop our return, on only the first request, and map our column positions:

jqGridDemo.js - populateGrid Ajax 'success' Method - figure 2

view plain print about
1success: function(d,r,o){
2    if(d.success){
3        if(!gridCols.set){
4            for(var i in d.data.COLUMNS){
5                gridCols[d.data.COLUMNS[i]] = parseInt(i);
6            }
7            gridCols.set = true;
8        }
9        ...
10    } else {
11        // If not, then do this
12    }
13}

If you dumped the gridCols object to the console after this, you would find a key for each column with a value of it's column position. All we did was loop the COLUMNS array, and set the key and position. We're going to need this let outside of these requests, but let's go ahead and use this object to remap the return data to our jqGrid columns:

jqGridDemo.js - populateGrid Ajax 'success' Method - figure 3

view plain print about
1success: function(d,r,o){
2    if(d.success){
3        // If loading for the first time, let's find out to which
4        // array positions our columns map.
5        if(!gridCols.set){
6            for(var i in d.data.COLUMNS){
7                gridCols[d.data.COLUMNS[i]] = parseInt(i);
8            }
9            gridCols.set = true;
10        }
11
12        grid.jqGrid('setGridParam',{remapColumns:[
13            gridCols['ID'],
14            gridCols['TITLE'],
15            gridCols['POSTED'],
16            gridCols['VIEWS']
17        ]});
18        grid[0].addJSONData(d);
19    } else {
20        // If not, then do this
21    }
22}

The remapColumns array is just a list of data positions, for which we used our new gridCols object. The addJSONData() method is then used to apply the return data to the grid. Run your demo template now to see the end result.

Voila! Data! You've just filled jqGrid with data from a remote ColdFusion request, and didn't even jump through hoops to do it. This is only the beginning. In our next post we'll talk in depth on the Column Model options, and about creating custom column formatting. You can find demo code in the Download link at the bottom of this post.