jqueryjsoncoldfusioncoldfusion-9jquery-easyui

ColdFusion 9, JSON, and jQuery EasyUI


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!


Solution

  • 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/