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.

Advertisements

19 thoughts on “Client-side sorting and pagination of an apex:pageBlockTable

    • Hi , I created the staticresource as mentioned and created the VF page and controller but some how the pagination and sorting controls are not showing up .

      Is there anything else that I need to be aware of ?

      • Rakesh, Because the work is done in JavaScript in the browser, you will need to debug the problem using Chrome’s “Developer Tools” (or whatever browser you are using’s equivalent). It is very rare (in my experience) to write JavaScript that works without debugging given that there is no compile step and its dynamic nature. Check that the JavaScript includes are all working and that there are no JavaScript errors. Also check that “j$(‘table.dataTable’)” (or whatever other expression you are using) is matching to your table. Good luck.

    • Hi Thank for the reply, I was able to fix the issue it is related to my static resource folder structure. I had one additional parent folder in it .. I got that rectified and its is working perfectly fine now.

      • If you mean a partial page update, then you have to start adding code to “oncomplete” to re-setup the jQuery logic. I’ve tried it once and eventually gave up and stick to jQuery or partial page updates but not both in one page.

  1. Hi,

    When I am trying to implement this example in my org. I’m constantly getting the following error: “Error: Compile Error: line 2:0 no viable alternative at character ‘ ‘ at line 2 column 0” and I am not understanding why it is happening so. Do you have anyplace from where I could download this sample code of yours.

  2. Hi I need help.
    I follow your tutorial, and I have a problem.
    I believe the problem is in load of the my static resource.
    My package is named DataTables, and when unzip him the name of folder is DataTables.
    My code its:

    .sorting {
    background: #f2f3f3 url(‘{! URLFOR($Resource.DataTables, ‘media/images/sort_both.png’) }’) no-repeat center right !important;
    padding-right: 20px !important;
    }
    .sorting_asc {
    background: #f2f3f3 url(‘{! URLFOR($Resource.DataTables, ‘media/images/sort_asc.png’) }’) no-repeat center right !important;
    padding-right: 20px !important;
    }
    .sorting_desc {
    background: #f2f3f3 url(‘{! URLFOR($Resource.DataTables, ‘media/images/sort_desc.png’) }’) no-repeat center right !important;
    padding-right: 20px !important;
    }
    .sorting_asc_disabled {
    background: #f2f3f3 url(‘{! URLFOR($Resource.DataTables, ‘media/images/sort_asc_disabled.png’) }’) no-repeat center right !important;
    padding-right: 20px !important;
    }
    .sorting_desc_disabled {
    background: #f2f3f3 url(‘{! URLFOR($Resource.DataTables, ‘media/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;
    }

    var j$ = jQuery.noConflict();
    j$(‘table.dataTable’).dataTable({
    sPaginationType: “full_numbers”
    });

    Do u can help me friend?

    Tnx.

  3. If everything is rooted in your zip in a DataTables folder then you could add that to the URLFOR paths e.g. URLFOR($Resource.DataTables, ‘DataTables/media/images/sort_both.png’) or just re-zip to get rid of it…

  4. I Have downloaded the Data tables plugin from the above and uploaded it into static resources with name jQueryDataTablesZip and i created class and page as same but sorting and pagination is not working.

    • Kalyan, You will have to debug the problem. The first thing to look at is your browser’s JavaScript console which will report any JavaScript errors. Other browser developer tools (such as the network activity) will also help you track down the problem(s). Also be very careful that the zip file content is correct – its easy to end up with e.g. an extra folder at the root.

  5. Hi,
    This plugin works like a charm !!!

    But there is a slight issue in my plugin. I am not able to see header sorting images. I have checked that images path is correct. When I dig into the solution it seems that my CSS is overriding. As per my knowledge you can override CSS by ‘important’ keyword which we are already using.

    Please tell me that what is causing issue at your earliest convenience.

  6. Hi ,
    I have tried your above solution , but it does not seem to be working. The js and css are added correctly as i can see those files while debugging from browsers. I am using the exact same code but i am only seeing the list of records in the page block table. No Sorting or pagination images , or buttons.
    Any help will be much appreciated.
    Thanks in Advance

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