How to query more than 50000 records in apex and bind it in vf page in jqgrid and paging?

Question

How to query more than 50000 records (78000 records) in apex and bind it in vf page in jqgrid and implement paging in it?

I tried querying through SOQL and assigning it to list.. Am unable to query more than 50000 records.

I tried offset but unfortunately offset doesnt accept more than 2000 as the number..

Tried database.getquerylocator and standardsetcontroller, but its returning only 10000 records and am able to bind it to jqgrid.

How to bind all the 78000 records to the page in jqgrid with paging (max 100 records per page)?

I have pasted the code below and in my assetcontroller am not able to give soql limit more than 10000. And I have even tried hitting the 100th Page (with each page having 100 records) and did con.getrecords() and again if I tried con.hasnext() it returns false, so it doesnt go beyond 10000 records at all..

@Jordan will it work if i give @remoteAction? if so what attributes (ex: datatype,url) do i need to change in my jqgrid ? and what else i need to do in my code?

// Visualforce page containing JQGrid :-

//Note AssetGridPage is a dummy page which will only return JSONresult given by the 
//controller and it is of contentType="application/x-JavaScript; charset=utf-8"

JQGridurl='https://c.cs11.visual.force.com/apex/AssetGridPage?core.apexpages.devmode.url=1&action=populateAssetSource';
var objectList = j$("#objectList").jqGrid(
                {
                        url: JQGridurl,                        
                        datatype:"json",
                        colNames: eval(ColNames),
                        colModel: eval(ColModel),
                        pager: '#objectListpager2',
                        toppager : true,                                                
                        rownumbers: true, 
                        gridview: true,
                        altRows: true,
                        enableSearch: false,
                        viewrecords: true,
                        sortname: 'AssetID',
                        sortorder: 'desc',              
                        rowNum:100,
                        rowList:[10,25,50,100],
                        ignoreCase:true,
                        //pagination:true,
                        loadtext:'Loading...'                        
                    }); 

// Asset Controller Code

                  
// Getter Method: JSONResult
    public String getJSONResult()
    {
        return returnJson;
    }

    public PageReference PageLoad() 
    {
        Map<string,string> params = ApexPages.currentPage().getParameters();
        if(params != null)
        {
            PageNum = Integer.ValueOf((params.get('page') != null)?params.get('page'):'1');
            PageSize = Integer.ValueOf((params.get('rows') != null)?params.get('rows'):'100');
            CurrentPage = Integer.ValueOf((params.get('page') != null)?params.get('page'):'1');
        }
        else 
        {
            PageNum = 1;
            PageSize = 100;
            CurrentPage = 1;
        }
        populateAssetSource();
        return null;
    }

    public void populateAssetSource()
    {   
        Decimal RecCount = 0;
        String SOQL = 'SELECT AssetID__c, AssetDescription__c FROM Asset__c limit 10000';
        con = new ApexPages.StandardSetController(Database.getQueryLocator(SOQL)); 
        con.setPageSize((Integer)PageSize);
        setPageNumber();
        // sets the number of records in each page set      
        //system.debug('RecCount::'+RecCount);
        //system.debug('PageSize::'+PageSize);
        //system.debug('CurrentPage::'+CurrentPage);
        if(con != null)
        {
            AllSearchAssets = getAllAssets((List<Asset__c>)con.getRecords());
            RecCount = con.getResultSize();
            String JSONString = JSON.serialize(AllSearchAssets);
            returnJson =  '{"total":' +  '"' + ((PageSize==0 && RecCount ==0)?0:(RecCount/PageSize).round(system.roundingMode.CEILING)) +'",';
            returnJson += '"page":' + '"' + ((RecCount == 0)?0:CurrentPage) +'",';
            returnJson += '"records":'  +  '"' + RecCount +'",';
            returnJson += '"rows" : ' + JSONString + '}';
        }
        else
        {
            returnJson = '{"total":"0","page":"0","records":"0","rows" : []}';
        }
    }

The following code is in AssetGridPage.. empty page which is hit by the jqgrid

<apex:page controller="AssetController" action="{!PageLoad}"
    contentType="application/x-JavaScript; charset=utf-8" showHeader="false" standardStylesheets="false" sidebar="false">
        {!JSONResult}
</apex:page>

@joshbirk so you are saying something like this?

List<asset__c> lstasset1 = Database.getquerylocator(Select id from asset__c where createddate > 'July-1-2012' and createddate < 'Aug-1-2012');

List<asset__c> lstasset2 = Database.getquerylocator(Select id from asset__c where createddate > 'Aug-1-2012' and createddate < 'Sep-1-2012');
lstasset1.addall(lstasset2);

And in the wrapper class of listasset1, add some pagenumber or some equivalent field. Foreach 100 increment the pagenumber variable.

Bind this lstasset1 to page based on some querystring filter and show only the 100. this is what you are saying right? if this is the case I cant go for a sorting of list. is there a possiblity to sort a list of wrapperclass type?

And one more limit we will be hitting is no. of query rows. we cant have more than 50000 records queried per transaction :(

So I guess even this solution wouldnt work.. Any Suggestions?

Answer 1

I've done jqGrid before in Apex with @RemoteActions and it's definitely the way to go. I think with Ajax toolkit and SOAP, you'll have too much XML bloat compared to JSON for large sets of records.

As for loading all 78000 records, I would say you should stick to the limits of loading 2000 records at a time and than breaking those into further subsets of 100 records / page on the client side.

To pagination with large record sets I would recommend using the standard set controller for pagination. Just make sure to include an order clause to keep the records returning in the same order.

Than load the next 2000 records in each subsequent call. And reduce the number of records you return from the server to speed things up in between 'set loads' as you get to the end of each set and need more pages.

You can also count the number of records and return it in a complex object from the @RemoteAction to keep your pagination accurate:

public class returnType{
  List<sObject> objects {get; set;}
  Integer totalRecords {get; set;}
  Integer nextPage {get; set;}
  Integer lastPage {get; set;}
}

UPDATE

Ok using the SSC to maintain pagination (at least this is the way I would do it with jqGrid:

@RemoteAction
global static returnType pagination(Integer pageNumber){
   ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(Database.getQueryLocator([SELECT Id FROM sObject ORDER BY Id LIMIT 50000]);
   ssc.setPageNumber(pageNumber);
   ssc.Pagesize(2000);

   returnType retVal = new returnType();
   retVal.objects = ssc.getRecords();
   retVal.nextPage = pageNumber + 1;
   retVal.previousPage = pageNumber - 1;

   return retVal;
}

I don't have my original code in front of me, but generally, the SSC should be re-instantiated with each call. As for getting past 50k records, and to your total set, the query locator should continue to the end of the set reguardless of size taking the larger records towards the end of the set as you get to the end of [0..n-1] pages.

In practice I would use an ORDER BY clause to ensure your records are consistently returned in the same order to ensure that your getting all results.

Answer 2

There's a lot to consider here:

1. You'll probably want to use JavaScript Remoting as the transport since you may need to be dealing with more than 1000 records at a time
2. If OFFSET can't work, you can use another field as a "marker". It depends on how your data is structured, how often it gets bulk loaded, etc. Some people have used a standard field like CreatedDate - others a custom autonumber. Both have their caveats but can generally be bludgeoned into working.
3. Technically, be wary of how much data you are putting into the interface at any one time. You can technically shove a lot into the page, but some browsers/scripts/devices will only be able to handle so much.

Answer 3

You can use the AJAX Toolkit to query via Salesforce's Web Service API via javascript. That will allow you to query any amount by pulling back chunks of up to 2000 records at a time using the query and queryMore calls.

See the AJAX Toolkit documentation here: http://www.salesforce.com/us/developer/docs/ajax/index.htm

Answer 4

Although I doubt you really want to load that many records in one go, you can set Read-Only mode on your Visualforce page to relax the limit on how many rows can be queried:

<apex:page controller="myController" readOnly="true">

Read-Only mode is really intended for use where you'll summarize the results, not to return zillions of rows.

See "Working with Large Sets of Data" in the Visualforce Developer's Guide for more information about Read-Only mode.