Connecting DataTables to JSON generated by Apex

I had a quick go at coming up with an answer to this DataTables sAjaxSource question on Salesforce Stackexchange but didn’t get very far. Using DataTable’s sAjaxSource directly runs you into the problem that the Visualforce page containing the table and the Apex REST API that supplies the JSON use different host names and so the same origin security policy blocks the access. (The answer includes one way to work-around this.)

The approach I tried was to use Apex’s @RemoteAction mechanism, where a JavaScript function is generated in the page. So instead of referencing a URL, the JavaScript function is called and how it connects back to the server is its concern. The trick to getting it to work is to recognise that the JavaScript function that you add (via the fnServerData property) is intended to provide an interception point for the request to the URL you specify (via the sAjaxSource property). So both must be specified, even though in this case the URL is not used.

This an example of the output (using default styles):

Here is the Visualforce page; most of the JavaScript is just cleaning up the JSON data returned from the controller to suit DataTables:

<apex:page controller="DataTableController">

<link
rel="stylesheet"
type="text/css"
href="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css"
/>

<apex:sectionHeader title="DataTables"/>

<table id="table" cellpadding="0" cellspacing="0" border="0">
    <thead>
        <th>Name</th>
        <th>Birthdate</th>
        <th>Phone</th>
        <th>Email</th>
        <th>Salary</th>
    </thead>
    <tbody>
    </tbody>
</table>

<script
type="text/javascript"
charset="utf8"
src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"
>
</script>
<script
type="text/javascript"
charset="utf8"
src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"
>
</script>
<script>

var j$ = jQuery.noConflict();

var fields = ['Name', 'Birthdate', 'Phone', 'Email', 'Salary__c'];

var aoColumns = [];
for (var i = 0; i < fields.length; i++) {
    aoColumns.push({'mData': fields[i]});
}

j$(document).ready(function() {
    j$('#table').dataTable({
        'aoColumns': aoColumns,
        'bProcessing': true,
        'bServerSide': true,
        'bFilter': false,
        'sAjaxSource': 'fakeUrl',
        'fnServerData': function(sSource, aoData, fnCallback) {
            console.log(JSON.stringify(aoData));
            // Call the @RemoteAction JavaScript function
            DataTableController.contacts(aoData, function(result, event) {
                if (event.type != 'exception') {
                    console.log(JSON.stringify(result));
                    for (var i = 0; i < result.aaData.length; i++) {
                        var r = result.aaData[i];
                        for (var j = 0; j < fields.length; j++) {
                            var field = fields[j];
                            if (r[field] == undefined) {
                                // DataTables pops a dialog for undefined values
                                r[field] = null;
                            } else if (field == 'Birthdate') {
                                // Dates transmitted as longs
                                var d = new Date(r[field]);
                                r[field] = ''
                                        + (d.getMonth() + 1)
                                        + '/'
                                        + d.getDate()
                                        + '/'
                                        + d.getFullYear()
                                        ;
                            }
                        }
                    }
                    // Call back into the DataTable function
                    fnCallback(result);
                } else {
                    alert(event.message);
                }
            });
        }
    });
});

</script>

</apex:page>

Most of the complexity in the Apex code is in interpreting the request parameters sent by DataTables including things like the multi-column sorting. Note that the conversion from JSON to Apex objects and Apex objects to JSON is left to the platform.

// See https://datatables.net/usage/server-side
global class DataTableController {

    // Defines shape of JSON response
    global class Response {
        public Integer sEcho;
        public Integer iTotalRecords;
        public Integer iTotalDisplayRecords;
        public SObject[] aaData;
        Response(Integer echo, Integer total, SObject[] sobs) {
            this.sEcho = echo;
            this.iTotalRecords = total;
            this.iTotalDisplayRecords = total;
            this.aaData = sobs;
        }
    }

    // DataTable passes JSON definition of what server should do
    private class Params {
    
        Map<String, Object> m = new Map<String, Object>();
        
        Integer echo;
        Integer start;
        Integer length;
        String[] columns;
        Integer[] sortColumns;
        String[] sortDirections;
        
        Params(List<Map<String, Object>> request) {
            for (Map<String, Object> r : request) {
                m.put((String) r.get('name'), r.get('value'));
            }
            echo = integer('sEcho');
            start = integer('iDisplayStart');
            length = integer('iDisplayLength');
            columns = stringArray('mDataProp');
            sortColumns = integerArray('iSortCol');
            sortDirections = stringArray('sSortDir');
        }
        
        String[] stringArray(String prefix) {
            String[] strings = new String[] {};
            for (Object o : array(prefix)) {
                strings.add(o != null ? esc(String.valueOf(o)) :null);
            }
            return strings;
        }
        
        Integer[] integerArray(String prefix) {
            Integer[] integers = new Integer[] {};
            for (Object o : array(prefix)) {
                integers.add(o != null ? Integer.valueOf(o) : null);
            }
            return integers;
        }

        Object[] array(String prefix) {
            Object[] objects = new Object[] {};
            for (Integer i = 0; true; i++) {
                Object o = m.get(prefix + '_' + i);
                if (o != null) {
                    objects.add(o);
                } else {
                    break;
                }
            }
            return objects;
        }
        
        Integer integer(String name) {
           Object o = m.get(name);
           if (o instanceof Decimal) {
               return ((Decimal) o).intValue();
           } else if (o instanceof Integer) {
               return (Integer) o;
           } else {
               return null;
           }
        }
        
        // Guard against SOQL injection
        String esc(String s) {
            return s != null ? String.escapeSingleQuotes(s) : null;
        }
    }
    
    @RemoteAction
    global static Response contacts(List<Map<String, Object>> request) {
    
        Params p = new Params(request);

        String soql = ''
                + ' select ' + String.join(p.columns, ', ')
                + ' from Contact'
                + ' order by ' + String.join(orderBys(p), ', ')
                + ' limit :length'
                + ' offset :start'
                ;
        System.debug('>>> soql=' + soql);

        Integer start = p.start;
        Integer length = p.length;
        return new Response(
                p.echo,
                [select Count() from Contact limit 40000],
                Database.query(soql)
                );
    }
    
    private static String[] orderBys(Params p) {
        Map<String, String> soqlDirections = new Map<String, String>{
                'asc' => 'asc nulls last',
                'desc' => 'desc nulls first'
                };
        String[] orderBys = new String[] {};
        Integer min = Math.min(p.sortColumns.size(), p.sortDirections.size());
        for (Integer i = 0; i < min; i++) {
            orderBys.add(''
                    + p.columns[p.sortColumns[i]]
                    + ' '
                    + soqlDirections.get(p.sortDirections[i])
                    );
        }
        return orderBys;
    }
}