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):

dt

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;
    }
}

Client-side sorting and pagination of an apex:pageBlockTable – more on sorting

OK so things looked a little too good to be true in Client-side sorting and pagination of an apex:pageBlockTable and I’ve had to do a bit more work to get the sorting working for datetime, date and currency columns. The problem is of course that the client-side JavaScript only has the formatted data available not the underlying (typed) data, and many formats are possible.

Below is my first cut at handling datetime, date and currency columns for the “English (United States)” locale. To reduce configuration, the type of a column is automatically detected rather than needing to be configured.

var j$ = jQuery.noConflict();

var sorting = {
    
    // Determine the format the column values are in
    typeDetect: function (a) {
        if (a) {
            if (a.match(/\d{1,2}\/\d{1,2}\/\d{4} \d{1,2}:\d{1,2} (AM|PM)/)) {
                return 'sf-datetime';
            } else if (a.match(/\d{1,2}\/\d{1,2}\/\d{4}/)) {
                return 'sf-date';
            } else if (a.match(/\(?\\$[\d\,\.]+\)?/)) {
                return 'sf-currency';
            }
        }
        return null;
    },
    
    // e.g. '8/17/2012 6:37 AM'
    datetimePre: function (a) {
        var b = a.match(/(\d{1,2})\/(\d{1,2})\/(\d{4}) (\d{1,2}):(\d{1,2}) (AM|PM)/);
        if (b && b.length == 7) {
            var month = b[1], day = b[2], year = b[3], hour = b[4], min = b[5], ap = b[6];
            if (hour == '12') hour = '0';
            if (ap == 'PM') hour = parseInt(hour, 10) + 12;
            if (month.length == 1) month = '0' + month;
            if (day.length == 1) day = '0' + day;
            if (hour.length == 1) hour = '0' + hour;
            if (min.length == 1) min = '0' + min;
            return year + month + day + hour + min;
        } else {
            return '';
        }
    },
    
    // e.g. '8/17/2012'
    datePre: function (a) {
        var b = a.match(/(\d{1,2})\/(\d{1,2})\/(\d{4})/);
        if (b && b.length == 4) {
            var month = b[1], day = b[2], year = b[3];
            if (month.length == 1) month = '0' + month;
            if (day.length == 1) day = '0' + day;
            return year + month + day;
        } else {
            return '';
        }
    },
    
    // e.g. '$1,019.05' or '($500.00)'
    currencyPre: function (a) {
        var b = a.match(/(\()?(\\$)([\d\,\.]+)(\))?/);
        if (b && b.length == 5) {
            return parseFloat((b[1] == '(' ? '-' : '') + b[3].replace(/\,/g, ''));
        } else {
            return 0;
        }
    },
    
    asc: function (a, b) {
       return a - b;
    },
    
    desc: function (a, b) {
       return b - a;
    }
}

// Register extra type detection
j$.fn.dataTableExt.aTypes.unshift(sorting.typeDetect);

// Register extra sorting functions
j$.extend(j$.fn.dataTableExt.oSort, {
    
    'sf-datetime-pre': sorting.datetimePre,
    'sf-datetime-asc': sorting.asc,
    'sf-datetime-desc': sorting.desc,
   
    'sf-date-pre': sorting.datePre,
    'sf-date-asc': sorting.asc,
    'sf-date-desc': sorting.desc,
   
    'sf-currency-pre': sorting.currencyPre,
    'sf-currency-asc': sorting.asc,
    'sf-currency-desc': sorting.desc
});

Client-side sorting and pagination of an apex:pageBlockTable

I’ve been using the jQuery tablesorter plugin for some time now in Visualforce pages and it has worked well. But recently a requirement came up to display multiple tables in a single Visualforce page and for each table to support (client-side) pagination. A quick try of the tablesorter pager plugin confirmed that it didn’t handle such a case well and so I followed the advice of moving to the jQuery DataTables plugin instead. The conversion only took a couple of hours.

Here is what I learned in the form of a contrived example. It produces this output:

DataTables

Step 1 is to create a zip file that has folders called “js”, “images” and “css” with those components copied from the “media” folder of the DataTables distribution. You should also include one of the license files. This is then uploaded as a static resource, in this example called “jQueryDataTablesZip”.

Step 2 is the controller Apex:

public with sharing class DemoController {
    public Contact[] getContacts() {
        return [
                select FirstName, LastName, Birthdate, Email, LastModifiedDate, OwnerId
                from Contact
                order by Name
                limit 500
                ];
    }
}

Step 3 is the Visualforce page:

<apex:page controller="DemoController">

<apex:stylesheet value="{!URLFOR($Resource.jQueryDataTablesZip, 'css/jquery.dataTables.css')}"/>
<style type="text/css">
.sorting {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_both.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_asc {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_asc.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_desc {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_desc.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_asc_disabled {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_asc_disabled.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_desc_disabled {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_desc_disabled.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
table.dataTable tr.odd { background-color: white; }
table.dataTable tr.even { background-color: white; }
table.dataTable tr.odd td.sorting_1 { background-color: white; }
table.dataTable tr.odd td.sorting_2 { background-color: white; }
table.dataTable tr.odd td.sorting_3 { background-color: white; }
table.dataTable tr.even td.sorting_1 { background-color: white; }
table.dataTable tr.even td.sorting_2 { background-color: white; }
table.dataTable tr.even td.sorting_3 { background-color: white; }
.dataTables_length, .dataTables_filter, .dataTables_info, .dataTables_paginate {
    padding: 3px;
}
</style>

<apex:sectionHeader title="Data Tables Demo"/>
<apex:pageBlock >
    <apex:pageBlockSection columns="1">
        <apex:pageBlockTable value="{!contacts}" var="c" styleClass="dataTable">
            <apex:column value="{!c.FirstName}"/>
            <apex:column value="{!c.LastName}"/>
            <apex:column value="{!c.Birthdate}"/>
            <apex:column value="{!c.Email}"/>
            <apex:column value="{!c.LastModifiedDate}"/>
            <apex:column value="{!c.OwnerId}"/>
        </apex:pageBlockTable>
    </apex:pageBlockSection>
</apex:pageBlock>

<script type="text/javascript" language="javascript" src="{!URLFOR($Resource.jQueryDataTablesZip, 'js/jquery.js')}"></script>
<script type="text/javascript" language="javascript" src="{!URLFOR($Resource.jQueryDataTablesZip, 'js/jquery.dataTables.js')}"></script>
<script type="text/javascript" language="javascript">
var j$ = jQuery.noConflict();
j$('table.dataTable').dataTable({
    sPaginationType: "full_numbers"
});
</script>

</apex:page>

Most of the work is fixing up the image URLs to reference the zip-based resources and tweaking the styling. Note that DataTable supports many other configuration options and looks pretty open for extension.

In summary, with next to no work you get:

  • sortable columns
  • pagination
  • search (filtering)

and no nasty surprises (at least so far for me) when your page becomes more complicated.

Executing jQuery.noConflict() mandatory when using jQuery in Visualforce

Visualforce pages presently always have the JavaScript library Prototype 1.6.0.3 automatically included. Other platform standard JavaScript such as RichFaces used by apex:tabPanel then rely on this library and the environment it creates such as a global function that has the name “$”.

So what happens when you also want to use jQuery in a Visualforce page? Generally, that is not a problem but only if as well as including jQuery you always execute the function jQuery.noConflict().

You’ll find plenty of posts talking about this, but the piece I didn’t understand is that jQuery caches the current value of “$” when you include it but only puts the cached value back in scope when you execute the jQuery.noConflict() function. So don’t put the jQuery.noConflict() inside conditional logic (or you will find yourself spending a couple of hours in Chrome’s JavaScript Console debugging as I did today).

FullCalendar using a JavaScript remoting callback

FullCalendar quick start illustrates a simple way to get FullCalendar working in a Visualforce page. All the event data was just emitted as JSON text in the generated page. But FullCalendar is typically used where the date range being displayed can be changed making that approach impractical. Two alternate ways to supply the event data are supported:

  • via a json feed – a GET url is configured
  • via a function – an arbitrary piece of JavaScript is configured

Both of these are passed the date range that is required for the calendar so that only the relevant events can be returned. The second of these provides more opportunity to do processing on the client side and is the approach described below.

Here is a simplified version of the Apex controller. The Event class models the values required for each event by FullCalendar and Apex automatically serializes the returned list of these as JSON because of the RemoteAction annotation. The arguments to the events method are two Apex Date primitives and an SObject (whose fields are displayed in the page and provide additional control over the calendar content):

public with sharing class CalendarController {
    
    public class Event {
    
        public String title;
        public String tip;
        public DateTime starts;
        public DateTime ends;
        
        public Event(String title, String tip, DateTime starts, DateTime ends) {
            this.title = title;
            this.tip = tip;
            this.starts = starts;
            this.ends = ends;
        }
    }

    // Page calls back into this
    @RemoteAction
    public static Event[] events(Date starts, Date ends, Filter__c filter) {

        System.debug('>>> starts=' + starts + ' ends=' + ends + ' filter=' + filter);

        List<Event> events = new List<Event>();

        // Loop goes here here where query results are turned into Event objects

        return events;
    }
}

Here is the change to what is assigned to “events” in the Visualforce page. Each time FullCalendar requires event data it invokes this JavaScript function. The function obtains values from the SObject fields in the page and passes those together with the start and end dates to the controller via the standard remote action call that automatically serializes the request as JSON. The returned results are passed into FullCalendar by invoking the callback function supplied in the original function call:

events: function(start, end, callback) {
    
    // JQuery references to the hidden id fields for lookups
    // and also to checkbox fields assigned to fields whose
    // names match the Filter__c SObject
    var filter = {
        Booking__c: bookingLkid.val(),
        Class__c: clazzLkid.val(),
        ShowAvailable__c: showAvailable.prop('checked'),
        ShowBooked__c: showBooked.prop('checked')
    };
    
    // JavaScript dates need formatting to be accepted at the Apex side
    Visualforce.remoting.Manager.invokeAction(
        '{!$RemoteAction.CalendarController.events}',
        start.toUTCString(),
        end.toUTCString(),
        filter, 
        function(result, event) {
            if (event.status) {
                // Jobs done here:
                // 1) In Apex "end" is a reserved word so using "ends"; change back
                // 2) The Apex DateTime is serialized as milliseconds; change to Date
                // 3) The HTML < and > are replaced by entities; turn back
                for (var i = 0; i < result.length; i++) {
                    var r = result[i];
                    r.start = new Date(r.starts);
                    r.end = new Date(r.ends);
                    r.tip = r.tip.replace(/&lt\;/g, '<').replace(/&gt\;/g, '>');
                }
                // Invoke FullCalendar function
                callback(result);
            } else {
                alert('ERROR:\n' + event.message + '\n' + event.where);
            }
        });
}

Gotchas along the way were (API 25.0):

  • Although the remote class and method being invoked are explicit in the call, if the class isn’t the controller or an extension, a page build error of “No remoted actions found to resolve…” results.
  • The automatic serialization of Date appears broken in both directions: see the JavaScript above for work-arounds; there may be similar issues with other types.
  • As the JSON serialization can’t be configured its awkward to generate JSON that includes any Apex reserved words.
  • The “tip” string is HTML generated by the Apex code and so needed some manipulation on the client-side to remove the encoding introduced by the serialization.
  • The Force.com documentation is short on details and examples so expect to spend time debugging to figure out what is actually happening.

Finally I’d like to mention that adding a tooltip to FullCalendar took less than 5 minutes and worked first time: all that was needed was the qTip JavaScript and this small addition:

eventRender: function(event, element) {
    element.qtip({
        content: event.tip
    });
}

FullCalendar quick start

I was pleasantly surprised at how painlessly Adam Shaw’s FullCalendar worked in a Visualforce page so am posting a basic controller and page here to help others get started. This is the output they produce (with of course all the hard work done by FullCalendar):

The controller emits hard-coded data: a real controller would build the data from various SObjects and provide actions to be invoked from the client-side JavaScript:

public with sharing class CalendarDemoController {
    public String events {
        get {
            JSONGenerator g = JSON.createGenerator(true);
            g.writeStartArray();
            for (Integer i = -2; i <= 2; i++) {
                g.writeStartObject();
                g.writeStringField('title', 'Event ' + i);
                g.writeDateTimeField('start', DateTime.now().addDays(i).addHours(i));
                g.writeDateTimeField('end', DateTime.now().addDays(i).addHours(i + 1));
                g.writeEndObject();
            }
            g.writeEndArray();
            return g.getAsString();
        }
    }
}

The fullcalendar-1.5.4.zip download is setup as a single static resource with the various pieces of content accessed via their paths in the Visualforce page. The calendar is rendered using a mixture of static configuration and the events JSON string provided by the controller:

<apex:page controller="CalendarDemoController" tabStyle="Contact">

<link href="{!URLFOR($Resource.fullcalendar,'fullcalendar-1.5.4/fullcalendar/fullcalendar.css')}" rel="stylesheet" type="text/css"/>
<link href="{!URLFOR($Resource.fullcalendar,'fullcalendar-1.5.4/fullcalendar/fullcalendar.print.css')}" rel="stylesheet" type="text/css" media="print"/>
<script src="{!URLFOR($Resource.fullcalendar, 'fullcalendar-1.5.4/jquery/jquery-1.8.1.min.js')}" type="text/javascript" language="javascript"></script>
<script src="{!URLFOR($Resource.fullcalendar, 'fullcalendar-1.5.4/fullcalendar/fullcalendar.min.js')}" type="text/javascript" language="javascript"></script>

<apex:sectionHeader title="Calendar Demo"/>

<!-- Calendar is rendered here by the fullcalendar Javascript -->
<apex:pageBlock >
    <div id='calendar'/>
</apex:pageBlock>

<!-- Fullcalendar configuration plus event data supplied by controller -->
<script type="text/javascript" language="javascript">
$(document).ready(function() {
    $('#calendar').fullCalendar({
        header: {
            left: 'title',
            center: 'month,agendaWeek,agendaDay',
            right: 'prevYear prev,today,next nextYear'
        },
        defaultView: 'agendaWeek',
        allDayDefault: false,
        minTime: 8,
        maxTime: 21,
        events:
{!events}
    });
});
</script>
</apex:page>