Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sucharita NandanSucharita Nandan 

Pagination in visualforce with soql offset

how can i disable next when there is the last page or disable previous when i am on the first page

this is my controller:
public with sharing class OppOPLiOffSetPagination {

public Opportunity opp { get;set; }
public Integer pageNumberOPLI = 0 ;
public List<opportunitylineitem> listOfOpli { get; set; }
private Integer pageSizeOPLI = 5;
public String oppId{ get;set; }

public OppOPLiOffSetPagination() {
this.opp = [ Select Id, Name, Account.Name, Amount
from Opportunity
where Id = :ApexPages.currentPage().getParameters().get('id')];
this.oppId = this.opp.Id;
this.listOfOpli = new List<opportunitylineitem>();

}

public PageReference opprtunityLIneitemList(){
queryOPLI();
return null;
}

private void queryOPLI(){
Integer OffsetSize = pageNumberOPLI *pageSizeOPLI;
String opliQuery = ' Select Product2.Id, TotalPrice, Quantity, Product2.ProductCode, UnitPrice,'+
                                    'Product2.Family, Product2.Name , OpportunityId from OpportunityLineItem'+
                                    ' where OpportunityId =:oppId limit ' + pageSizeOPLI + ' offset ' + OffsetSize;
        this.listOfOpli = Database.query(opliQuery);

}

public PageReference Next() {
pageNumberOPLI++;
queryOPLI();
//enableDisablePaginationLinks();
return null;
}

public PageReference Previous() {
pageNumberOPLI--;
if (pageNumberOPLI < 0)
return null;
queryOPLI();
return null;
}

}

and here is my page:
<apex:page showheader="true" sidebar="true" controller="OppOPLiOffSetPagination">
<apex:form>
<apex:pageblock title="Opportunity Detail">
<apex:pageblocksection>
<apex:outputtext label="Name" value="{!opp.Name}"/>
<apex:outputtext label="Amount" value="{!opp.Amount}"/>
<apex:outputtext label="Account Name" value="{!opp.Account.Name}"/>
</apex:pageblocksection>
</apex:pageblock>
<apex:pageblock id="pgBlock">
<apex:pageblockbuttons>
<apex:commandbutton action="{!opprtunityLIneitemList}" value="Show Opps" rerender="pgBlock"/>
</apex:pageblockbuttons>
<apex:pageblocktable title="OPLI Detail" value="{!listOfOpli}" var="opli" id="pgTable">
<apex:column value="{!opli.Product2.Id}"/>
<apex:column value="{!opli.TotalPrice}"/>
<apex:column value="{!opli.Quantity}"/>
<apex:column value="{!opli.UnitPrice}"/>
<apex:column value="{!opli.Product2.Name}"/>
</apex:pageblocktable>
<apex:pageblockbuttons>
<apex:commandbutton value="Previous" action="{!Previous}" rerender="pgTable,pgBlock" status="status"/>
<apex:commandbutton value="Next" action="{!Next}" rerender="pgTable,pgBlock" status="status"/>
</apex:pageblockbuttons>
</apex:pageblock>
</apex:form>
</apex:page>
ArmouryArmoury
You can have two boolean variables like hasNext and hasPrevious which can be set to true/false in Next(), Previous() and in constructor methods.
Use these boolean variables in the <apex:commandbutton> code using rendered attribute.
Swayam@SalesforceGuySwayam@SalesforceGuy
Hi,

Refer below link to get an Idea

https://www.sundoginteractive.com/blog/server-side-pagination-in-apex-visualforce-for-custom-classes

Hope this help,

--
Regards,
Swayam
@salesforceguy
Sucharita NandanSucharita Nandan
Hi Armoury,

I have tried below code , but it is not working properly

controller:

public with sharing class OppOPLiOffSetPagination {

    public Opportunity opp { get;set; }
    public Integer pageNumberOPLI = 0 ;
    public List<OpportunityLineItem> listOfOpli { get; set; }
    private Integer pageSizeOPLI = 5;
    public String oppId{ get;set; }
    public Integer totalOPLI{ get; set; }
    public Integer currentPageOPLI { get; set; }
    public Boolean showPrevious {get;set;}
    public Boolean showNext {get;set;}


    public OppOPLiOffSetPagination() {
        this.opp = [ Select Id, Name, Account.Name, Amount
                                    from Opportunity
                                    where Id = :ApexPages.currentPage().getParameters().get('id')];
        this.oppId = this.opp.Id;
        this.listOfOpli = new List<OpportunityLineItem>();
        this.totalOPLI = 0;
        this.currentPageOPLI = 0;
        this.showPrevious = true;
        this.showNext = true;
    }



    public PageReference opprtunityLIneitemList(){
        queryOPLI();
        return null;
    }

    private void queryOPLI(){
        Integer OffsetSize = (this.pageNumberOPLI  * this.pageSizeOPLI);
        String opliQuery = ' Select Product2.Id, TotalPrice, Quantity, Product2.ProductCode, UnitPrice,'+
                                    'Product2.Family, Product2.Name , OpportunityId from OpportunityLineItem'+
                                    ' where OpportunityId =:oppId limit ' + pageSizeOPLI + ' offset ' + OffsetSize;
        this.listOfOpli = Database.query(opliQuery);
        this.totalOPLI = this.listOfOpli.size();
        if( totalOPLI>pageSizeOPLI ){
            this.showNext = true;
        }else{
            this.showNext = false;
        }
    }

 
    public PageReference Next() {
        pageNumberOPLI++;
        queryOPLI();
        enableDisablePaginationLinks();
        return null;
    }
 
    public PageReference Previous() {
        pageNumberOPLI--;
        if (pageNumberOPLI < 0)
            return null;
        queryOPLI();
        enableDisablePaginationLinks();
        return null;
    }


    public void showNext(){
        currentPageOPLI = currentPageOPLI - pageSizeOPLI;
        pageNumberOPLI--;
        queryOPLI();
        enableDisablePaginationLinks();
    }


    public void showPrevious(){
        currentPageOPLI = currentPageOPLI + pageSizeOPLI;
        pageNumberOPLI++;
        queryOPLI();
        enableDisablePaginationLinks();
    }

    private void enableDisablePaginationLinks(){
        queryOPLI();
        if( currentPageOPLI ==0 ){
            showPrevious = false;
        }else{
            showPrevious =true;
        }
        if( ( totalOPLI - pageSizeOPLI ) <= currentPageOPLI ){
            showNext = false;
        }else{
            showNext = true;
        }
    }
}


Vf Page:
<apex:page showHeader="true" sidebar="true" controller="OppOPLiOffSetPagination">
    <apex:form>
        <apex:pageBlock title="Opportunity Detail">
            <apex:pageBlockSection>
                <apex:outputText label="Name" value="{!opp.Name}"/>
                <apex:outputText label="Amount" value="{!opp.Amount}"/>
                <apex:outputText label="Account Name" value="{!opp.Account.Name}"/>
            </apex:pageBlockSection>
        </apex:pageBlock>
        <apex:pageBlock id="pgBlock">
            <apex:pageBlockButtons>
                <apex:commandButton action="{!opprtunityLIneitemList}" value="Show Opps" reRender="pgBlock"/>
            </apex:pageBlockButtons>
            <apex:pageBlockTable title="OPLI Detail" value="{!listOfOpli}" var="opli" id="pgTable">
                <apex:column value="{!opli.Product2.Id}"/>
                <apex:column value="{!opli.TotalPrice}"/>
                <apex:column value="{!opli.Quantity}"/>
                <apex:column value="{!opli.UnitPrice}"/>
                <apex:column value="{!opli.Product2.Name}"/>               
            </apex:pageBlockTable>
            <apex:pageBlockButtons >
                <apex:commandButton value="Previous" action="{!Previous}" rendered="{!!showPrevious}" rerender="pgTable,pgBlock"
                                    status="status" />
                <apex:commandButton value="Next" action="{!Next}" rendered="{!!showNext}" reRender="pgTable,pgBlock"
                                    status="status" />
            </apex:pageBlockButtons>
        </apex:pageBlock>
    </apex:form>
</apex:page>
ArmouryArmoury
I think the main issue is setting up the totalOPLI.. The program should know how many total records are there when you click 'ShowOpps' button.. For example if the Opporutnity has total 100 Opportunity line items and each page you are displaying only 5 records at a time, then this totalOPLI has to be set to 100 in the opprtunityLIneitemList()  method. This is one time set and it should not be altered when clicking next or previous buttons. So you can use count() query like below to set it. 
Integer totalOPLI = database.countQuery('Select count() from OpportunityLineItem where OpportunityId = :oppId');
Then u can use this count in other methods to track whether there are any more records available or not..