[SalesForce] Implement pagination on APEX REST Endpoints

I am working on a Apex REST framework that acts as an abstraction layer to make it easier for all our other systems to consume data from salesforce (i.e. consistent naming scheme for field names, abstraction for complex filters, pre-filtered lists, different representations for the same SObjects under differend endpoints, etc) compared to the standard REST Api.

However, I struggle with implementing proper pagination. The usual approach to use limit & offset as query parameters does not seem to work, since we have way over 50k records in certain SObjects, but the SOQL OFFSET only allows a maximum value of 2000.

I read about SOQL in for-Loops, which internally "utilizes SOAP query() and queryMore()", but I have not been able to implement this functionality with Apex directly.

How to implement pagination? I don't necessarily need limit/offset, if there are better patterns to utilize standard platform functionality, I would be very happy to use them.

Best Answer

Why not design your own querry cursor in Apex?

Theory: Salesforce ID fields are autoincremented when created and behave like a number so you can use Less than and Greater than operators.

So if you can send the lastRecordID to your rest endpoint you can create your own querry cursor.

eg.

Select id from Opportunity where  Id >= '00658000003rJFwAAM' order by Id Limit 2000

is a perfectly valid SOQL and will return all records having ID greater than 00658000003rJFwAAM. Thus this can be used for the Pagination logic in your stateless rest endpoint

Always store the last recordId and use that ID to get next set of records, No need to use offset anymore. Given that you design stateful client and stateless server.

This method is used for querying large data volume when the normal SOQL with where clause on other fields timeout.

Source: https://developer.salesforce.com/blogs/developer-relations/2015/11/pk-chunking-techniques-massive-orgs.html

Related Solutions

[SalesForce] Apex REST response with reserved words

Salesforce Apex and other languages like Java, C etc will give you errors when you try to use reserved words.

However as you said that this response is really required so here is what I suggest.

Rename the list to something unique example: list_replace_me
```
global List<DataWrapperClass> list_replace_me;
```

Instead of returning the wrapper from your GET method, return a string.

global static string getList() { 
//Manually serialize this object

new ResponseWrapperClass(); }

Manually serialize the object by JsonGenerator class to get a string, or use JSON.serialize(inputobject) which return a string.
Replace "list_replace_me" with "list" using String.replace function.
Return the new String after replacing.

Json Generator

Differences between REST API Endpoints

Endpoints that are of the form /services/apexrest/xxxxx are custom APEX-coded endpoints allowing you to do whatever logic you want. You can design your own URL params for GET methods or design your own body (JSON or otherwise) for POST, PUT, PATCH, DELETE methods. A common pattern is for the Apex REST endpoint to take the input and marshal one or more service layer methods in a Separation of Concerns architecture.
Endpoints of the form /services/data/vXX.X/... are examples of the OOTB SFDC REST API. There are many other flavors besides updating a single record as you discovered including APIs to:
- Query for objects (SOQL and SOSL)
- Do Describes on objects
- Create objects
- Delete objects
- Do DML on batches of objects including related objects (see Composite Resources)
- and plenty more

The OOTB REST endpoints don't require you to write any APEX at all. The client simply conforms to the specification as documented in the REST API guide and, after authenticating, just sends the HTTP request. Salesforce does the work (the DML or query/describe) and returns an HTTP status code and result body (in JSON).

Best Answer

Related Solutions

[SalesForce] Apex REST response with reserved words

Differences between REST API Endpoints

Related Topic