Apply pagination in Rest Resource

Question

I have rest resource exposed to a external system. It is a simple rest api where i get a single parameter from the URL and query a custom object based on that param and return the results in a custom wrapper class.

This custom object has more that 150000 records. now the external system is asking for pagination. Please let me know how do i achieve this.

I tried using StandardSetController and i got an error saying No Visualforce context has been established!

please find the sample code below without pagination...

@RestResource(urlMapping='/custom_info/*')
global class custom_Service{

    @HttpGet
    Webservice static void getAssetListForSelectedUser(){
        RestRequest req = RestContext.request;
        RestResponse res = RestContext.response;

        customWrapper result = new customWrapper(); 
        String federationId=req.params.get('recid');

        User usr = new User();
        usr = [Select Id, Name, LanguageLocaleKey from User where FederationIdentifier = :federationId];

        List<Asset__c> assets = [Select Id, Name, createddate, serial__c, Description__c from Asset__c where ownerId = : usr.id Limit 30000];

        result.name = usr.name;
        result.name = usr.LanguageLocaleKey;
        result.assets = assets;

        res.statusCode = 200;
        res.responseBody = Blob.valueOf(JSON.serialize(result));
        return;
    }

    global class customWrapper {
        public String name{get;set;}
        public String language{get;set;}
        public List<Asset__c> assets{get;set;}

        public customWrapper(){}

    }
}

Below is the sample code with pagination using standardsetcontroller. For pagination i receive 2 more params offset and limit. But i am getting No Visualforce context has been established! error.

@RestResource(urlMapping='/custom_info/*')
global class custom_Service{

    @HttpGet
    Webservice static void getAssetListForSelectedUser(){
        RestRequest req = RestContext.request;
        RestResponse res = RestContext.response;

        customWrapper result = new customWrapper(); 
        ApexPages.StandardSetController setController = null;
        String federationId=req.params.get('recid');
        String pageSize = req.params.get('limit');
        String pageNumber = req.params.get('offset');

        User usr = new User();
        usr = [Select Id, Name, LanguageLocaleKey from User where FederationIdentifier = :federationId];

        List<Asset__c> assets = [Select Id, Name, createddate, serial__c, Description__c from Asset__c where ownerId = : usr.id];

        setController = new ApexPages.StandardSetController(assets);
        if((pageSize != '' || pageSize != null) && (pageNumber != '' || pageNumber != null)) {
            setController.setPageSize(Integer.valueOf(pageSize));
            setController.setPageNumber(Integer.valueOf(pageNumber));
        }else {
            setController.setPageSize(1000);
            setController.setPageNumber(1);
        }

        result = new customWrapper(usr.name, usr.LanguageLocaleKey, setController);
        result.name = usr.name;
        result.name = usr.LanguageLocaleKey;
        result.assets = assets;

        res.statusCode = 200;
        res.responseBody = Blob.valueOf(JSON.serialize(result));
        return;
    }

    global class customWrapper extends PaginatedResult {
        public String name{get;set;}
        public String language{get;set;}
        public List<Asset__c> assets{get;set;}

        public customWrapper(){}

        public customWrapper (String name, String lan, ApexPages.StandardSetController c) {
            super(c);
            name = name;
            language = lan;
            for (Asset__c a : (Asset__c[])c.getRecords()) {
                assets.add(a);
            }
        }
    }

    public virtual class PaginatedResult {
    public Integer page; 
    public Integer pageSize;
    public Integer pageCount;
    public Integer total;

    public PaginatedResult() {}

    public PaginatedResult(ApexPages.StandardSetController c) {

      page = c.getPageNumber();
      total = c.getResultSize();
      calcPagination(c.getPageSize());
    }

    public void calcPagination(Integer pageSize) {
      this.pageSize = pageSize;
      pageCount = total / pageSize;
      pageCount = (Integer)(Math.floor(pageCount) + ((Math.mod(total, pageSize) > 0) ? 1 : 0));
    }
  }
}

Please help....

Answer 1

You will have to use the [OFFSET][1] keyword in your SOQL query for Assets. However, the problem you will run into is that the OFFSET keyword has a max value of 2000. Salesforce does have a workaround for this.

Answer 2

I would suggest you do the following:

• Add an autonumber field to Asset
• Adapt the queryMore pattern used in OOTB REST.

• Change your query to look something like this:

  ... WHERE MyAutoNumberField__c > urlParamOffset
      ORDER BY MyAutoNumberField__c
      LIMIT 1000 // or whatever pgsize you need to use
  

where `urlParamOffset defaults to zero but otherwise is supplied by the REST client because you pass it back as a response to the client the last autoNumberField fetched in the query (i.e. the 1000th record retrieved)