OFFSET limit exceeds while implementing pagination for listing of users

Question

Currently, we are trying to populate all users of salesforce in a Visualforce Page with page per limit of 1000 using Database.getQueryLocator (shown below)

Database.getQueryLocator('select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000')

As the capacity to fetch the getQueryLocator is of 10,000 I can't event directly fire a query without using limit clause (shown below)

   String query= 'select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name' ;
   StandardSetController  con = new ApexPages.StandardSetController(Database.getQueryLocator(query)); 

Now, the problem is currently in system there are around 62 thousand users active and in order to provide the next , prev , hasNext , hasPrev feature I need to perform the displacement of 1000 records every time user presses next or prev button on visualforce page

1. Fetch first thousand records

select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000 OFFSET 0

2. Fetch next thousand records

select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000 OFFSET 1000

3. Fetch next thousand records

select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000 OFFSET 2000

The last query will fail and throws following error. So , can any one help how would I float per page 1000 records and allow user to go till 62,000 by pressing next

Answer 1

You can store the Name of the last record of your query result, and pass the name into your SOQL query when the Prev/Next button is clicked.

Also update the map values along when user clicks on the Next button.

Map<Integer, String> nameByPageNo = new Map<Integer, String>{
    1 => '<1000th Name>',
    2 => '<2000th Name>'
}

Next button SOQL would be:

SELECT Name 
FROM User 
WHERE Name > :nameByPageNo.get(pageNo)
ORDER BY Name LIMIT 1000

Prev button SOQL would be:

SELECT Name 
FROM User 
WHERE Name > :nameByPageNo.get(pageNo-1)
ORDER BY Name LIMIT 1000