+ Start a Discussion
Sameer PrasonnSameer Prasonn 

SOQL Results pagination

I need to prepare result pagination. I must keep records limit to 100 for each page and once i'm done and click next It must  fetch next 100 records and display next 100 records. I'm on the same. If any one can help that would be great Thank you in advance.
Best Answer chosen by Sameer Prasonn
Sameer PrasonnSameer Prasonn
Thank you for your help guys, specially Deepak. 

What I ended up doing was the following:
1. introduced a new field of type autonumber in the object, this helps to maintain the serial results.
2. fix the limit to 50 as following.
private final Integer PAGE_SIZE=50;

3. get the min for each iteration.
4. fire an SOQL Query for each iteration with the limit which i have declared above PAGE_SIZE
5. change the min each time and setup the max for the next iteration. that is on the basis of new field we have introduce in step 1.
6. total pages can be measured by get total no of results, that we can achieve via count.

The reson for this approach is it will never exceed the limit of offset ever. 

I'll post the complete code for this scenario in my later post. hope this helps other as well.

Happy programming. keep on rocking guys.

All Answers

Vijaya Kumar RegantiVijaya Kumar Reganti
Hi,

Please find the following the following page and the Class code which I have done way back.

Page Code:
*************

<apex:page controller="Sample" >
<style>
    .ButtonCls{
        colour:blue;
    }
</style>
<apex:form >
    <apex:pageBlock id="details" title="Members Available" helpUrl="https://help.salesforce.com" helpTitle="Help for this Page">
    <table width="100%">
        <apex:outputLabel style="color:red;font-size:15px">Total Records : {!count}</apex:outputLabel><br></br>       
    </table>
        <apex:pageblockTable value="{!memb}" var="m">
            <apex:column value="{!m.Name}"/>
            <apex:column value="{!m.Age__c}"/>          
        </apex:pageblockTable>
        <apex:pageblockButtons >
            <apex:commandButton value="<<Start" rerender="details" action="{!beginning}" disabled="{!prev}" styleclass="ButtonCls" />
            <apex:commandButton value="<Previous" rerender="details" action="{!previous}" disabled="{!prev}" styleclass="ButtonCls"/>
            <apex:commandButton value="Next>" rerender="details" action="{!next}" disabled="{!nxt}" styleclass="ButtonCls"/>
            <apex:commandButton value="End>>" rerender="details" action="{!end}" disabled="{!nxt}" styleclass="ButtonCls"/>                                  
        </apex:pageblockButtons>
    </apex:pageBlock>
</apex:form>
</apex:page>


Controller:
*********************

public class Sample
{  
    public integer totalRecs = 0;   
    public integer index = 0;
    public integer blockSize = 5;       
    public integer count {get;set;}
   
    public sample()
    {
        totalRecs = [select count() from Member__c];
        count = totalRecs;     
    }  
  
    public List<Member__c> getMemb()
    {
        List<Member__c> membs = [SELECT Name, Age__c FROM Member__c LIMIT :blockSize OFFSET :index];
        System.debug('Values are ' + membs);
        return membs;
    }  
  
    public void beginning()
    {
        index = 0;
    }
  
    public void previous()
    {
        index = index - blockSize;
    }
  
    public void next()
    {
        index = index + blockSize;
    }

    public void end()
    {
        index = totalrecs - math.mod(totalRecs,blockSize);
    }      
  
    public boolean getprev()
    {
        if(index == 0)
        return true;
        else
        return false;
    }
  
    public boolean getnxt()
    {
        if((index + blockSize) > totalRecs)
        return true;
        else
        return false;
    }       
}

Regards,
Vijay Kumar
Deepak Kumar ShyoranDeepak Kumar Shyoran
You can skip those record by using Offset in follwing way in your Query. 

Ex : SELECT fieldLis FROM objectType [WHERE conditionExpression] ORDER BY fieldOrderByList LIMIT number_of_rows_to_return OFFSET number_of_rows_to_skip

But as Offset have Limit 2000 so you can only skip 2000 records using Offset if you're working on large set of data then I'll suggest you to filter your record based on Current records.

List<Account> acc = new List<Account>() ;
 acc =  [Select id,Name from Account where id <> :acc Limit 5 ] ;

// This will fetch next 5 records after previous 5
acc =  [Select id,Name from Account where id <> :pp Limit 5 ] ;

Please mark my answer as a best solution to your question to help others if it solves your problem
Shyam BhundiaShyam Bhundia
You can use StandardSetController for this (https://www.salesforce.com/us/developer/docs/pages/Content/apex_pages_standardsetcontroller.htm).  
You can use setPageSize(100) to get 100 records at a time.  Check out the StandardSetController methods to see how to implement the next/prev etc.. methods.
Sameer PrasonnSameer Prasonn
Well guys I way Deepak answer the question. and we should agree on offset aspect the it will last till number 2000. so i have another solution for the same. which i'm going to post in a couple of days since It take some time for me write the code with exact scenario. till then take care and have more coding stuff.
Deepak Kumar ShyoranDeepak Kumar Shyoran
Please mark my answer as a best solution to your question to help others if it solves your problem

Sameer PrasonnSameer Prasonn
Thank you for your help guys, specially Deepak. 

What I ended up doing was the following:
1. introduced a new field of type autonumber in the object, this helps to maintain the serial results.
2. fix the limit to 50 as following.
private final Integer PAGE_SIZE=50;

3. get the min for each iteration.
4. fire an SOQL Query for each iteration with the limit which i have declared above PAGE_SIZE
5. change the min each time and setup the max for the next iteration. that is on the basis of new field we have introduce in step 1.
6. total pages can be measured by get total no of results, that we can achieve via count.

The reson for this approach is it will never exceed the limit of offset ever. 

I'll post the complete code for this scenario in my later post. hope this helps other as well.

Happy programming. keep on rocking guys.
This was selected as the best answer
umesh atryumesh atry
We can achieve by using Two ways :
1.) Using StandardSet Controller
2.) Soql with LIMIT and OFFSET