VF page showing salesforce pagination using datatables

Question

I'm trying to create a salesforce list view pagination using datatables, in my visualforce page. In my case, I have lets say 10K records and like on salesforce I want to show not more that 2000+ records at a time.

Clicking Next button at datatables pagination I go to the last page where I'm shown 1900-2000 of 2000 records. When I click next again I want to go to query the next 2000 records from 2000 - 4000.

I'm using javascript remote action and loading all 2000 records in the page.

Does anybody have an example of this situation? How have you handled the code to understand the last next of 2000 records?

Answer 1

Since OFFSET clause accepts maximum value of 2000, we can't use it for a big case scenario.

Instead, we need to implement custom pagination.

First, query for all records Id.

[SELECT Id FROM YourStandardOrCustomObject__c <you could include WHERE or ORDER BY clause here>]

Next, you divide it by 2000 manually and find the Id values list for a given page.

Then you make another query for all the fields you need based by given 2000 Id values list for the records from a given page.

Answer 2

I've done this and below you can find snippets of my code. I also provided the portion (with comments) where I initially set pageSize to 25 records as 2,000 rows rendering takes some time and I didn't want users to stare at a blank page while it's being rendering...

Apex Controller

public with sharing class myController {

    // 2,000 is maximum value out of max 10,000 records returned by StandardSetController
    public Integer pageSize { get; private set; }
    public Integer recordsCount {get; private set;}

    public List<mySObject__c> mySObjectRecords
    {
        get
        {
            return (List<mySObject__c>) mySObjectSetController.getRecords();
        }
        private set;
    }

    public ApexPages.StandardSetController mySObjectSetController
    {
        get
        {
        // mySObject__c records are queried only once, and kept in a SetController
        // this helps to overcome 1,000 records limit for list rendered on VF page:
        // SetController limit is 10,000, with max 2,000 returned on a single page
            if(mySObjectSetController == null)
            {

                mySObjectSetController = new ApexPages.StandardSetController(
                    Database.getQueryLocator([SELECT Id... FROM mySObject__c]);
                );

                recordsCount = mySObjectSetController.getResultSize();
            }
            mySObjectSetController.setPageSize(pageSize);
            return mySObjectSetController;
        }
        private set;
    }

    public myController(ApexPages.StandardController sController)
    {
        sc = sController;

        // Set to 25 records for fast initialization, then UpdateSetPageSize is called
        // to change page size to 2,000 (max value) records, if SetController size is over, 
        // Prev/Next buttons are displayed (in addition to datatable pagination) to scroll
        pageSize = 25;
    }  

    public pageReference UpdateSetPageSize()
    {
        pageSize = 2000; //2000
        return null;
    }
}

Visualforce Page

<apex:page standardcontroller="mySObject__c" extensions="myController"
           id="myPage" doctype="html-5.0" >

    <apex:variable value="{!mySObjectSetController}" var="setCon" />

    <apex:form id="mainForm">
        <!-- This allows to defer Table rendering for all records that takes some time... -->
    <apex:actionfunction name="DeferredLoad" action="{!UpdateSetPageSize}" rerender="TablePanel"
                         oncomplete="enforceDataTable(); j$.unblockUI();" />
        <apex:outputpanel id="TablePanel">
            <table id="myTable" class="display" cellspacing="0" width="100%">
                <apex:repeat value="{!mySObjectRecords}" var="records" >
                    <!-- your table rendering routine -->
                </apex:repeat>
            </table>

            <apex:panelgrid columns="7" rendered="{!pageSize < recordsCount}">
                <apex:commandbutton status="fetchStatus" rerender="mainForm" value=" |< " oncomplete="enforceDataTable();"
                                    action="{!setCon.first}" disabled="{!!setCon.hasPrevious}" title="First Page" />
                <apex:commandbutton status="fetchStatus" rerender="mainForm" value=" < " oncomplete="enforceDataTable();"
                                    action="{!setCon.previous}" disabled="{!!setCon.hasPrevious}" title="Previous Page" />
                <apex:outputtext style="vertical-align: middle !important; padding: 0px 20px !important;"
                                 value="{!((setCon.pageNumber - 1) * pageSize) + 1} -
            {!MIN((setCon.pageNumber * pageSize),recordsCount)} of {!recordsCount}" />
                <apex:commandbutton status="fetchStatus" rerender="mainForm" value=" > " oncomplete="enforceDataTable();"
                                    action="{!setCon.next}" disabled="{!!setCon.hasNext}" title="Next Page" />
                <apex:commandbutton status="fetchStatus" rerender="mainForm" value=" >| " oncomplete="enforceDataTable();"
                                    action="{!setCon.last}" disabled="{!!setCon.hasNext}" title="Last Page" />
            </apex:panelgrid>

        </apex:outputpanel>
    </apex:form>
<script>
    var myTable = '';
    j$ = jQuery.noConflict();

    j$(document).ready( function ()
   {
        enforceDataTable();
    });

    function enforceDataTable(){
        myTable = j$('[id$="myTable"]').DataTable({
            //your DataTable initialization routine
        } );
    }
</script>
</apex:page>