ColdFusion 9, JSON, and jQuery EasyUI

Question

I'm trying to output a ColdFusion query to JSON so that it can be used with jQuery EasyUI (specifically a Datagrid).

From the example .json files that come with EasyUI this is the format they are looking for...

{"total":2
  , "rows":[ 
            { "productid":"FI-SW-01"
              , "productname":"Koi"
              , "unitcost":10.00
              , "status":"P"
              , "listprice":36.50,"attr1":"Large"
              , "itemid":"EST-1"
             }
          , { "productid":"K9-DL-01"
             , "productname":"Dalmation"
             , "unitcost":12.00 
             , "status":"P"
             , "listprice":18.50
             , "attr1":"Spotted Adult Female"
             , "itemid":"EST-10"
            }
        ]
 }

However when I use SerializeJSON(emails) on a ColdFusion query I get this:

{ "COLUMNS":["CUSTOMERID","CUSTOMERFIRSTNAME"]
   , "DATA":[
               [101,"Bhavin"],[102,"Frank"]
            ]
}

This does not seem to be recognized by EasyUI, so I guess the questions are (1) Should EasyUI be able to recognize and work with the output from ColdFusion as shown, or (2) Is there a way to make ColdFusion output the JSON in a format like the one included in the EasyUI example?

Update:

This is what it looks like if I use the serializeQueryByColumns parameter:

{ "ROWCOUNT":83
  , "COLUMNS":["CUSTOMERID","CUSTOMERFIRSTNAME"]
  , "DATA":{
            "CUSTOMERID":[101,102]
            ,"CUSTOMERFIRSTNAME":["Bhavin","Frank","]
            }
}

Still not recognized by EasyUI though. In their documentation they show a php example that looks like this, so it's the output of this that I would be trying to replicate with ColdFusion I guess:

$rs = mysql_query('select * from users');
$result = array();
while($row = mysql_fetch_object($rs)){
    array_push($result, $row);
}

echo json_encode($result);

Thanks!

Doesn't look like your query has all the columns you're going to need! I'd say you'll have to create it manually, e.g. how would you expect SerializeJSON() to add the query.RecordCount into the 'total'? — duncan, Mar 04 '14 at 14:43
Since you'll probably need to do this over and over, I'd suggest wrapping the functionality into a UDF. Once you're finished, you should also consider submitting it to CFLib.org, as there are probably others who could benefit from your work. Another similar to yours is: http://www.cflib.org/udf/queryConvertForjqGrid — existdissolve, Mar 04 '14 at 15:02
Duncan, if I use the serializeQueryByColumns option it does actually expose the record count as so... ROWCOUNT":2. So the function is able to handle that. — Bryan, Mar 04 '14 at 15:31

score 6 · Accepted Answer · edited Mar 05 '14 at 01:54

The loadFilter function of datagrid can convert any data to the standard data format that can be loaded into datagrid.

<script>
var data = { "ROWCOUNT":83
  , "COLUMNS":["CUSTOMERID","CUSTOMERFIRSTNAME"]
  , "DATA":{
            "CUSTOMERID":[101,102]
            ,"CUSTOMERFIRSTNAME":["Bhavin","Frank"]
            }
};
function myLoadFilter(data){
    var result = {total:data.ROWCOUNT};
    var rows = [];
    var count = data.DATA[data.COLUMNS[0]].length;
    for(var i=0; i<count; i++){
        var row = {};
        $.map(data.COLUMNS, function(field){
            row[field] = data.DATA[field][i];
        })
        rows.push(row);
    }
    result.rows = rows;
    return result;
}
</script>

The datagrid component can be declared as:

    <table class="easyui-datagrid" title="Basic DataGrid" style="width:700px;height:250px"
        data-options="
            singleSelect:true,
            collapsible:true,
            data:data,
            loadFilter:myLoadFilter
        ">
    <thead>
        <tr>
            <th data-options="field:'CUSTOMERID',width:100">CUSTOMERID</th>
            <th data-options="field:'CUSTOMERFIRSTNAME',width:200">CUSTOMERFIRSTNAME</th>
        </tr>
    </thead>
</table>

The example is available from http://jsfiddle.net/d8zYy/

That's awesome, thank you! This btw is in response to my emailing their support, nice to see people supporting their products. Hope this can help others out also. — Bryan, Mar 05 '14 at 12:30

score 4 · Answer 2 · answered Mar 04 '14 at 14:51

It doesn't look like the EasyUI DataGris will accept custom formats for data (which is a shame, as the datagrid looks very nice apart from that)

It ought to be straightforward to convert a CF Query into the right format for EasyUI:

Create a struct to hold the data
set struct.total=query.RecordCount (if that's what it wants. For remote pagination, it's more likely to be the total results, regardless of current page)
set struct.rows=ArrayNew(1)
loop through the query (<cfloop query='myQuery'>
create a new struct, rowData
loop through the fields ('myQuery.columnList')
set struct.columnName = myQuery['columnName']
ArrayAppend(struct.rows,rowData)
return struct

I'm beginning to suspect that writing that list above took about as long as writing the actual function...

ColdFusion 9, JSON, and jQuery EasyUI

2 Answers2