function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
thunksalotthunksalot 

Query subset of total rows at a time?

I'm working with a VF page and Apex controller and I want to be able to do SOQL queries that are limited to 200 results but that start the results at different places in the total query results.  In other words have queries that would get me the first 200 results, the second 200 results and so on...  BUT, I want to be able to jump around so that the user could jump straight from the 1st page of results (records 1-200) to the 5th page of results (query for results 1,000 to 1,200).  Is that possible?  I cannot query *all* the records and then just return 200 to the page at a time because the query returns too much data.  So, I'd want to run the query again each time but getting a specific subset.   Kind of like a row limit but starting at a certain record count.  

 

Totally crazy?

Best Answer chosen by Admin (Salesforce Developers) 
kriskkrisk

You will need to implement your own pagination in the VF controller class using 2 features in SOQL - LIMIT & OFFSET

 

Say if you have 100 rows of CONTACT data and in a Query you want to bring back 31 to 40 rows

 

You will issue query as 'SELECT ID, NAME FROM CONTACT ORDER BY NAME LIMIT 10 OFFSET 30'

 

On your VF page you can implement either 

 

4 command buttons FIRST, PREV, NEXT, LAST OR

 

command links to the Page Numbers that call appropriate action that does the pagination.

 

You can create a custom field to store either the Current Offset or Page Number and Total Results

 

Clicking the PREV, NEXT button will call VF controller and pass current offset or page number

 

Clicking FIRST and LAST will return same data. 

 

You would do your calculations within the VF controller

 

 

All Answers

kriskkrisk

You will need to implement your own pagination in the VF controller class using 2 features in SOQL - LIMIT & OFFSET

 

Say if you have 100 rows of CONTACT data and in a Query you want to bring back 31 to 40 rows

 

You will issue query as 'SELECT ID, NAME FROM CONTACT ORDER BY NAME LIMIT 10 OFFSET 30'

 

On your VF page you can implement either 

 

4 command buttons FIRST, PREV, NEXT, LAST OR

 

command links to the Page Numbers that call appropriate action that does the pagination.

 

You can create a custom field to store either the Current Offset or Page Number and Total Results

 

Clicking the PREV, NEXT button will call VF controller and pass current offset or page number

 

Clicking FIRST and LAST will return same data. 

 

You would do your calculations within the VF controller

 

 

This was selected as the best answer
thunksalotthunksalot

It looks like OFFSET is only available as a Developer Preview.  I've submitted a request to have it turned on for my org.  Hopefully, SF will comply. 

 

Thank you, Krisk and Shaleish for your quick responses.  Since you both pointed me to the same solution, I had a hard time deciding who to give credit for the answer.  I decided to give it to Krisk, even though Krisk's answer came in a few minutes later, because Krisk provided a little more guidance.  

kriskkrisk

You are welcome. Give kudos if it really helped you