DataTables for SalesForce (DT2Apex and DataTableController)

If you've ever read any posts on my blog - or spent longer than about 5 minutes talking to me for that matter - you'll know that I love DataTables. You'll also probably know that we at work use SalesForce a lot. I've used DataTables quite a bit in Visualforce pages to display data and such-like as well as in projects outside of work and within work but not using SalesForce but I've never been able to figure out a way of displaying either Standard or Custom objects using them... until last week that is.

There are a couple of steps involved in getting DataTables to work within SalesForce, I bow to Keith Clarke's Connecting DataTables to JSON generated by Apex and Daniel's DATATABLES IN VISUALFORCE, PART 1 to give me a place from where to get started.

DataTables Server-side processing rocks in the PHP applications I've written but there's something of a learning curve as the server-side script needs to be able to read the JSON that DataTables is going to send to it and it also needs to send back information in the correct JSON format in order for it to be displayed. Both of these formats are described here.

In order for SalesForce to understand the JSON being sent to it I made use of the really rather marvellous JSON2Apex to generate this class:

//
// Generated by JSON2Apex http://json2apex.herokuapp.com/
//
// The supplied json has fields with names that are reserved words in apex
// and so can only be parsed with explicitly generated code, this option
// was auto selected for you.

public class DT2Apex {
    public static void consumeObject(JSONParser parser) {
        Integer depth = 0;
        do {
            JSONToken curr = parser.getCurrentToken();
            if (curr == JSONToken.START_OBJECT || 
                curr == JSONToken.START_ARRAY) {
                depth++;
            } else if (curr == JSONToken.END_OBJECT ||
                curr == JSONToken.END_ARRAY) {
                depth--;
            }
        } while (depth > 0 && parser.nextToken() != null);
    }

    public class Order {
        public Integer column {get;set;} 
        public String dir {get;set;} 

        public Order(JSONParser parser) {
            while (parser.nextToken() != JSONToken.END_OBJECT) {
                if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                    String text = parser.getText();
                    if (parser.nextToken() != JSONToken.VALUE_NULL) {
                        if (text == 'column') {
                            column = parser.getIntegerValue();
                        } else if (text == 'dir') {
                            dir = parser.getText();
                        } else {
                            System.debug(LoggingLevel.WARN, 'Order consuming unrecognized property: '+text);
                            consumeObject(parser);
                        }
                    }
                }
            }
        }
    }
    
    public class Search_Z {
        public String value {get;set;} 
        public Boolean regex {get;set;} 

        public Search_Z(JSONParser parser) {
            while (parser.nextToken() != JSONToken.END_OBJECT) {
                if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                    String text = parser.getText();
                    if (parser.nextToken() != JSONToken.VALUE_NULL) {
                        if (text == 'value') {
                            value = parser.getText();
                        } else if (text == 'regex') {
                            regex = parser.getBooleanValue();
                        } else {
                            System.debug(LoggingLevel.WARN, 'Search_Z consuming unrecognized property: '+text);
                            consumeObject(parser);
                        }
                    }
                }
            }
        }
    }
    
    public Integer draw {get;set;} 
    public List<Columns> columns {get;set;} 
    public List<Order> order {get;set;} 
    public Integer start {get;set;} 
    public Integer length {get;set;} 
    public Search_Z search_Z {get;set;} // in json: search
    public String object_Z {get;set;} // in json: object

    public DT2Apex(JSONParser parser) {
        while (parser.nextToken() != JSONToken.END_OBJECT) {
            if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                String text = parser.getText();
                if (parser.nextToken() != JSONToken.VALUE_NULL) {
                    if (text == 'draw') {
                        draw = parser.getIntegerValue();
                    } else if (text == 'columns') {
                        columns = new List<Columns>();
                        while (parser.nextToken() != JSONToken.END_ARRAY) {
                            columns.add(new Columns(parser));
                        }
                    } else if (text == 'order') {
                        order = new List<Order>();
                        while (parser.nextToken() != JSONToken.END_ARRAY) {
                            order.add(new Order(parser));
                        }
                    } else if (text == 'start') {
                        start = parser.getIntegerValue();
                    } else if (text == 'length') {
                        length = parser.getIntegerValue();
                    } else if (text == 'search') {
                        search_Z = new Search_Z(parser);
                    } else if (text == 'object') {
                        object_Z = parser.getText();
                    } else {
                        System.debug(LoggingLevel.WARN, 'Root consuming unrecognized property: '+text);
                        consumeObject(parser);
                    }
                }
            }
        }
    }
    
    public class Columns {
        public String data {get;set;} 
        public String name {get;set;} 
        public Boolean searchable {get;set;} 
        public Boolean orderable {get;set;} 
        public Search_Z search_Z {get;set;} // in json: search

        public Columns(JSONParser parser) {
            while (parser.nextToken() != JSONToken.END_OBJECT) {
                if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                    String text = parser.getText();
                    if (parser.nextToken() != JSONToken.VALUE_NULL) {
                        if (text == 'data') {
                            data = parser.getText();
                        } else if (text == 'name') {
                            name = parser.getText();
                        } else if (text == 'searchable') {
                            searchable = parser.getBooleanValue();
                        } else if (text == 'orderable') {
                            orderable = parser.getBooleanValue();
                        } else if (text == 'search') {
                            search_Z = new Search_Z(parser);
                        } else {
                            System.debug(LoggingLevel.WARN, 'Columns consuming unrecognized property: '+text);
                            consumeObject(parser);
                        }
                    }
                }
            }
        }
    }
    
    
    public static DT2Apex parse(String json) {
        return new DT2Apex(System.JSON.createParser(json));
    }
}

With this I was laughing! This is the corresponding test class:

//
// Generated by JSON2Apex http://json2apex.herokuapp.com/
//

@IsTest
public class DT2Apex_Test {
    
    // This test method should give 100% coverage
    static testMethod void testParse() {
        String json = '{'+
        '    \"draw\": 1,'+
        '    \"columns\": ['+
        '        {'+
        '            \"data\": \"Name\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Street__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Post_Town__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Postcode__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Address__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"UPRN__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        }'+
        '    ],'+
        '    \"order\": ['+
        '        {'+
        '            \"column\": 0,'+
        '            \"dir\": \"asc\"'+
        '        }'+
        '    ],'+
        '    \"start\": 0,'+
        '    \"length\": 10,'+
        '    \"search\": {'+
        '        \"value\": \"\",'+
        '        \"regex\": false'+
        '    },'+
        '    \"object\": \"Location__c\"'+
        '}';
        DT2Apex r = DT2Apex.parse(json);
        System.assert(r != null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex.Order objOrder = new DT2Apex.Order(System.JSON.createParser(json));
        System.assert(objOrder != null);
        System.assert(objOrder.column == null);
        System.assert(objOrder.dir == null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex.Search_Z objSearch_Z = new DT2Apex.Search_Z(System.JSON.createParser(json));
        System.assert(objSearch_Z != null);
        System.assert(objSearch_Z.value == null);
        System.assert(objSearch_Z.regex == null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex objRoot = new DT2Apex(System.JSON.createParser(json));
        System.assert(objRoot != null);
        System.assert(objRoot.draw == null);
        System.assert(objRoot.columns == null);
        System.assert(objRoot.order == null);
        System.assert(objRoot.start == null);
        System.assert(objRoot.length == null);
        System.assert(objRoot.search_Z == null);
        System.assert(objRoot.object_Z == null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex.Columns objColumns = new DT2Apex.Columns(System.JSON.createParser(json));
        System.assert(objColumns != null);
        System.assert(objColumns.data == null);
        System.assert(objColumns.name == null);
        System.assert(objColumns.searchable == null);
        System.assert(objColumns.orderable == null);
        System.assert(objColumns.search_Z == null);
    }
}

Keith Clarke used an older version of DataTables so I didn't need to provide a fake sAjaxSource but other than that my client-side script was quite similar to his. I basically pass the ajax option a function like this:

"processing": true,
"serverSide": true,
"ajax": function (data, callback, settings) {
    data.object = "<YOUR OBJECT NAME HERE>";
    DataTableController.getdata(
        JSON.stringify(data), 
        function(result, event) {
            if (event.type != 'exception') {
                callback(result);
            } else {
                alert(event.message);
            }
        }
    )
}

This means that my Controller (DataTableController) needs to grab the functionality of DT2Apex in order to understand what DataTables wants. It sort of knows the structure of the data it needs to return so that DataTables can understand it and display it properly (Response). This is DataTableController:

global class DataTableController {
    // https://force201.wordpress.com/2014/03/15/connecting-datatables-to-json-generated-by-apex/
    @RemoteAction
    @ReadOnly
    global static Response getData(String request) {
        // Use our nice parser from http://json2apex.herokuapp.com/
        DT2Apex parsedRequest = DT2Apex.parse(request);
        // Get the total number of records
        Integer recordsTotal = Database.countQuery('SELECT COUNT() FROM ' + parsedRequest.object_Z);
        // Get our field names
        List<String> fieldNames = new List<String>();
        for (DT2Apex.Columns c : parsedRequest.columns) {
            fieldNames.add(c.data);
        }
        // Sort out our ordering using field names
        List<String> orderBy = new List<String>();
        for (DT2Apex.Order o : parsedRequest.order) {
            orderBy.add(fieldNames.get(o.column) + ' ' + o.dir);
        }
        if(parsedRequest.search_Z.value != ''){
            List<String> stringField = new List<String>();
            String type=parsedRequest.object_Z;
            Map<String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
            Schema.SObjectType leadSchema = schemaMap.get(type);
            Map<String, Schema.SObjectField> fieldMap = leadSchema.getDescribe().fields.getMap();
            for (String fieldName: fieldMap.keySet()) {
                //It provides to get the object fields label.
                String fieldLabel = fieldMap.get(fieldName).getDescribe().getLabel();
                //It provides to get the object fields data type.
                Schema.DisplayType fielddataType = fieldMap.get(fieldName).getDescribe().getType();
                if(fielddataType == Schema.DisplayType.String){
                    stringField.add(fieldName);
                }
            }
            List<String> whereClause = new List<String>();
            for (String f : stringField) {
                whereClause.add(f + ' LIKE \'%' + parsedRequest.search_Z.value + '%\'');
            }
            // Get the total number of records
            Integer recordsFiltered = Database.countQuery('SELECT COUNT() FROM ' + parsedRequest.object_Z + ' WHERE ' + String.join(whereClause, ' OR '));
            // Our query with WHERE:
            String soql = ' SELECT ' + String.join(fieldNames, ', ')
                    + ' FROM ' + parsedRequest.object_Z
                    + ' WHERE ' + String.join(whereClause, ' OR ')
                    + ' ORDER BY ' + String.join(orderBy, ', ')
                    + ' LIMIT ' + parsedRequest.length
                    + ' OFFSET ' + parsedRequest.start;
            return new Response(
                parsedRequest.draw, 
                recordsTotal,
                recordsFiltered,
                Database.query(soql)
            );
        }else{
            // Our boring query:
            String soql = ' SELECT ' + String.join(fieldNames, ', ')
                + ' FROM ' + parsedRequest.object_Z
                + ' ORDER BY ' + String.join(orderBy, ', ')
                + ' LIMIT ' + parsedRequest.length
                + ' OFFSET ' + parsedRequest.start;
            // Our response!
            return new Response(
                parsedRequest.draw, 
                recordsTotal,
                recordsTotal,
                Database.query(soql)
            );
        }
    }
    global class Response {
        public Integer draw;
        public Integer recordsTotal;
        public Integer recordsFiltered;
        public SObject[] data;
        Response(Integer draw, Integer total, Integer totalQuery, SObject[] sobs) {
            this.draw = draw;
            this.recordsTotal = total;
            this.recordsFiltered = totalQuery;
            this.data = sobs;
        }
    }
}

It makes use of @ReadOnly in order to be able to query the COUNT(*) of objects with more than 50,000 records and, so long as we don't play silly-bugger's and allow the user to display more than too many records, we should be fine!

The nice thing about it is we can define our own Object (<YOUR OBJECT NAME HERE>) so the same class can be used to generate tables for Apples and Oranges.

All the hard work is done in the client-side script in terms of asking for the data; we're just using a single class to produce data for any number of objects. A working example is this:

<apex:page controller="DataTableController">
    <head>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/jqc-1.11.3,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,b-1.0.3,b-flash-1.0.3,b-html5-1.0.3,b-print-1.0.3/datatables.min.css"/>
    </head>
    <body>
        <table id="contactsTable" class="display"></table>
        <script type="text/javascript" src="https://cdn.datatables.net/r/dt/jqc-1.11.3,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,b-1.0.3,b-flash-1.0.3,b-html5-1.0.3,b-print-1.0.3/datatables.min.js"></script>
        <script>
            j$ = jQuery.noConflict();
            j$(document).ready( function () {
                var locationTable = j$('[id$="contactsTable"]').DataTable({
                    "dom": "Blfrtip",
                    "buttons": [
                        "copy", "csv", "pdf"
                    ],
                    "columns":[
                        {
                            "title":"ID",
                            "data": "Id",
                            "visible": false
                        },{
                            "title":"Name",
                            "data": "Name",
                            "render": function(data, type, row, meta){
                                return j$("<a></a>",{
                                    "text":data,
                                    "href": "../"+ row.Id
                                }).prop('outerHTML')
                            }
                        },{
                            "title":"Department",
                            "data":"Department",
                            "defaultContent":""
                        },{
                            "title":"Owner",
                            "data":"Owner.Name"
                        }
                    ],
                    "processing": true,
                    "serverSide": true,
                    "filter": true,
                    "ajax": function (data, callback, settings) {
                        data.object = "Contact";
                        DataTableController.getData(
                            JSON.stringify(data), 
                            function(result, event) {
                                if (event.type != 'exception') {
                                    callback(result);
                                } else {
                                    alert(event.message);
                                }
                            }
                        )
                    }
                });
            });
        </script>
    </body>
</apex:page>

This makes use of the new CDN for DataTables and also includes the new buttons code - which so far seems to work a treat - it also gives nice added extras so you can export the data as a PDF or CSV or copy all visible rows to the clipboard. Altogether this rocks!

I've been doing something with locations objects with Easting and northing so I've used Chris Veness's Geodesy functions within a columns render function to make a link to Google Maps with the correct Latitude and Longitude.

My next job is to try and write a test class for DataTableController, I've spent a wee bit of time trying to figure out how to write one but I seem to be stumped - any ideas would be most welcome.