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

18 thoughts on “Connecting DataTables to JSON generated by Apex

  1. Impressive work! I just tried it and it works perfectly.
    It seems a little bit more work on parsing the data and parameters, than my solution, but it seems better practice, than making a response VF page.

    Thank you.

    • Yeah DataTables seems overly fussy about missing data, and on the Apex side if you let the platform do the serialization to JSON you don’t have any control there either. Whatever approach you decide to take I hope this helps. I thought this was going to be easier than it turns out to be…

  2. Hi,

    Thanks for this very good article. When I was researching on how to use DataTables with Salesforce, most of the answers were about using a dedicated VF page to return the proper JSON to DataTables. But I knew there would be a better solution using JS Remoting and here it is. Thanks a lot!

    One question: how would you dynamically generate the data for ‘aoColumns’? In your example, you are setting it manually in the JS client-side.

    I’m thinking on querying a fieldset (passed in parameter) to retrieve the fields that I want to query for the data. However, I’m wondering if I can return both ‘aoColumns’ and ‘aoData’ in the ‘fnCallback’ method.

    This is to avoid making two JS Remote calls (one for the ‘aoColumns’ and the second to get the data just like in your example).

    Thanks for any insight!

    • Mickael,

      It is my understanding that the aoColumns data isn’t something that can be varied once the table is setup. But you could certainly generate the JavaScript text for that in your controller based on the fields you want displayed and render that as part of the page using e.g.:

      var fields = {!fieldsJson};
      

      Is that sufficient?

      • Hi,

        You are correct, aoColumns data is fixed after the table is setup, however, the way you indicated how to get the fields variable from the Apex controller worked like a charm.

        Thanks a lot!

  3. Good morning,

    I just wanted to point out that the maximum offset is 2,000 rows according to the documentation:
    http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_offset.htm

    Which is annoying, since the standard query can return up to 50k records. Which means that you can hit the offset as soon as the 20th page (on a 100/page configuration) by using datatable.

    I came with the solution of querying all the records (with a LIMIT 50000 statement) assuming they are always ordered by something. Then manually creating a sublist and passing that as the returned list of SObject.

    In your code, this would be:

    List returned = Database.query(soql);
    List subList = new List();
    for (Integer i = start; i < start + length; i++) {
        try {
            subList.add(returned.get(i));
        } catch (System.ListException ex) {
            break;
    }
    

    If you can find of a better way, I’m all ears.

    Thanks for your articles!

    • I would suggest adding something like this:

      } else if (typeof r[field] === 'boolean') {
          r[field] = r[field] ? '<img src="..." />' : '<img src="..." />';
      

      where you have a pair of images, one for checked and one for unchecked.

      • Hi,

        Thanks for your response

        I need dynamical check boxes for the above displaying records.

        For Example if i click on custom button, i need to show the number of records with check boxes and then i need to select few records. After selecting records, i will click on enter and then all the selected records will be stored into database.

        For the above requirement, can we use the above example or please provide me some help links to solve the above issue.

  4. Pingback: Return string value of id on jQuery datatable server side sorting - DL-UAT

  5. Amazing, this is really good work, thank you so mcuh for sharing this!!!
    if you dont mind, I would like to ask you 2 questions:
    1. just before return new Response (around line 100), wouldnt be better to have something like
    List lstContacts= new List();
    lstContacts = Database.query(soql);

    this way you can pass as a parameters:
    lstContacts.size()
    instead of passing 2 queries?

    2. I have never used global or @remote, could you please guide me on how ot build the test class?

    • Fabian,

      On 1), one query is returning a page of results e.g. 100 and the other the total number of contacts e.g. 23,456 so they are really doing different things.

      On 2), as far as tests are concerned global is just like public so you can invoke global methods directly from tests. In tests you just reference objects like Response directly: the serialization that @remote provides never needs to be exercised in the tests because that platform feature is already well tested.

      Good luck!
      Keith

      • Oh I see, I just test it, and doing it the way I mention, it will not paginate properly.

        That is really good that we dont have to worry about testing @remote 🙂

        Also can I ask, maybe this is a datatable issue, but if I change bFilter from false to true (‘bFilter’: true), it doesnt works.
        Could you please advise me how to make the filter works

        again thank you for sharing this is gold 🙂

      • On the filtering, I suspect it is that when you pull the data from the server-side you have to filter the data you return from there. I suggest your search DataTables forums for more information on that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s