Apex Code Development You need to sign in to do that
Don't have an account?
Matt Cooper 7 VIsualforce Search Page - SOQL Results Pagination
Hi, I've been building a VF search page and have run into a slight issue. I want to add pagination so that I can return all of my results and allow users to go through the results at 20 records a page. I have been trying to look at instructions online and can't seem to link my results with the pages. Can anyone take a look and provide me some insight into how I could get pagination with my code? Thanks!
Controller:
VF Page:
Controller:
public with sharing class CorpGovSearchController {
private integer counter=0; //keeps track of the offset
private integer list_size=20; //sets the page size or number of rows
public integer total_size; //used to show user the total size of the list
public Apttus__APTS_Agreement__c agmt1 {get;set;}
// the soql without the order and limit
private String soql {get;set;}
// the collection of agreements to display
public List<Apttus__APTS_Agreement__c> agmts {get;set;}
// the current sort direction. defaults to asc
public String sortDir {
get { if (sortDir == null) { sortDir = 'asc'; } return sortDir; }
set;
}
// the current field to sort by. defaults to last name
public String sortField {
get { if (sortField == null) {sortField = 'Name'; } return sortField; }
set;
}
// format the soql for display on the visualforce page
public String debugSoql {
get { return soql + ' order by ' + sortField + ' ' + sortDir + ' LIMIT 100';}
set;
}
// init the controller and display some sample data when the page loads
public CorpGovSearchController() {
soql = 'select Name, Nike_SF_Contract_Category__c, Apttus__Agreement_Category__c, NikeSF_Sub_Geography__c, NikeSF_Geography__c, ID, Apttus__Status_Category__c, Apttus__Status__c, NikeSF_Agreement_Type__c from Apttus__APTS_Agreement__c where name != null';
runQuery();
}
// toggles the sorting of query from asc<-->desc
public void toggleSort() {
// simply toggle the direction
sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
// run the query again
runQuery();
}
// runs the actual query
public void runQuery() {
try {
agmts = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' LIMIT 100');
total_size= agmts.size();
} catch (Exception e) {
ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
}
}
public void reset() {
agmt1.NikeSF_Sub_Geography__c='';
}
// runs the search with parameters passed via Javascript
public PageReference runSearch() {
String Name = Apexpages.currentPage().getParameters().get('Name');
String agmtPurpose = Apexpages.currentPage().getParameters().get('agmtPurpose');
String agmtCategory = Apexpages.currentPage().getParameters().get('agmtCategory');
String subgeography = Apexpages.currentPage().getParameters().get('subgeography');
String geography = Apexpages.currentPage().getParameters().get('geography');
String ID = Apexpages.currentPage().getParameters().get('ID');
soql = 'select Name, Nike_SF_Contract_Category__c, Apttus__Agreement_Category__c, NikeSF_Sub_Geography__c, NikeSF_Geography__c, ID, Apttus__Status_Category__c, Apttus__Status__c, NikeSF_Agreement_Type__c from Apttus__APTS_Agreement__c where name != null';
if (!Name.equals(''))
soql += ' and Name LIKE \'%'+String.escapeSingleQuotes(Name)+'%\'';
if (!agmtCategory.equals(''))
soql += ' and Nike_SF_Contract_Category__c LIKE \''+agmtPurpose+'\'';
if (!agmtCategory.equals(''))
soql += ' and Apttus__Agreement_Category__c LIKE \''+agmtCategory+'\'';
if (!geography.equals(''))
soql += ' and NikeSF_Geography__c LIKE \'' + ''+geography+'\'';
if (!subgeography.equals(''))
soql += ' and NikeSF_Sub_Geography__c LIKE \''+subgeography+'\'';
// run the query again
runQuery();
return null;
}
public PageReference Beginning() { //user clicked beginning
counter = 0;
return null;
}
public PageReference Previous() { //user clicked previous button
counter -= list_size;
return null;
}
public PageReference Next() { //user clicked next button
counter += list_size;
return null;
}
public PageReference End() { //user clicked end
counter = total_size - math.mod(total_size, list_size);
return null;
}
public Boolean getDisablePrevious() {
//this will disable the previous and beginning buttons
if (counter>0) return false; else return true;
}
public Boolean getDisableNext() { //this will disable the next and end buttons
if (counter + list_size < total_size) return false; else return true;
}
public Integer getTotal_size() {
return total_size;
}
public Integer getPageNumber() {
return counter/list_size + 1;
}
public Integer getTotalPages() {
if (math.mod(total_size, list_size) > 0) {
return total_size/list_size + 1;
} else {
return (total_size/list_size);
}
}
}
VF Page:
<apex:page controller="CorpGovSearchController" sidebar="false">
<apex:form >
<apex:pageMessages id="errors" />
<apex:pageBlock title="Agreement Search" mode="edit">
<table width="100%" border="0">
<tr>
<td width="200" valign="top">
<apex:pageBlock title="Filters" mode="edit" id="criteria">
<script type="text/javascript">
var oldG;
var oldAP;
function doSearch() {
if(document.getElementById("{!$Component.geography}").value=='' || document.getElementById("{!$Component.subgeography}").value=='__' || document.getElementById("{!$Component.geography}").value!=oldG){
document.getElementById("{!$Component.subgeography}").value='';
}
if(document.getElementById("{!$Component.agmtPurpose}").value=='' || document.getElementById("{!$Component.agmtCategory}").value=='__' || document.getElementById("{!$Component.agmtPurpose}").value!=oldAP){
document.getElementById("{!$Component.agmtCategory}").value='';
}
oldG = document.getElementById("{!$Component.geography}").value;
oldAP = document.getElementById("{!$Component.agmtPurpose}").value;
searchServer(
document.getElementById("Name").value,
document.getElementById("{!$Component.agmtPurpose}").value,
document.getElementById("{!$Component.agmtCategory}").value,
document.getElementById("{!$Component.geography}").value,
document.getElementById("{!$Component.subgeography}").value
);
}
</script>
<apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">
<apex:param name="Name" value="" />
<apex:param name="agmtPurpose" value="" />
<apex:param name="agmtCategory" value="" />
<apex:param name="geography" value="" />
<apex:param name="subgeography" value="" />
</apex:actionFunction>
<table cellpadding="2" cellspacing="2">
<tr>
<td style="font-weight:bold;">Agreement Name<br/>
<input type="text" id="Name" onkeyup="doSearch();"/>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Agreement Purpose<br/>
<apex:inputfield id="agmtPurpose" value="{!agmt1.Nike_SF_Contract_Category__c}" onchange="doSearch();"/>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Agreement Category<br/>
<apex:inputfield id="agmtCategory" value="{!agmt1.Apttus__Agreement_Category__c}" onchange="doSearch();"/>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Geography<br/>
<apex:inputfield id="geography" value="{!agmt1.NikeSF_Geography__c}" onchange="doSearch();"/>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Sub-Geography<br/>
<apex:inputfield id="subgeography" value="{!agmt1.NikeSF_Sub_Geography__c}" onchange="doSearch();"/>
</td>
</tr>
</table>
</apex:pageBlock>
</td>
<td valign="top">
<apex:pageBlock mode="edit" id="results">
<apex:pageBlockButtons location="both" >
<apex:outputPanel id="myButtons">
<apex:commandButton action="{!Beginning}" title="Beginning" value="<<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>
<apex:commandButton action="{!Previous}" title="Previous" value="<Previous" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>
<apex:commandButton action="{!Next}" title="Next" value="Next>" disabled="{!disableNext}" reRender="myPanel,myButtons"/>
<apex:commandButton action="{!End}" title="End" value=">>" disabled="{!disableNext}" reRender="myPanel,myButtons"/>
</apex:outputPanel>
</apex:pageBlockButtons>
<apex:pageBlockTable value="{!agmts}" var="agmt">
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Agreement Name" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="Name" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputLink value="/{!agmt.id}">{!agmt.Name}</apex:outputLink>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Agreement Category" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="Apttus__Agreement_Category__c" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!agmt.Apttus__Agreement_Category__c}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Agreement Type" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="NikeSF_Agreement_Type__c" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!agmt.NikeSF_Agreement_Type__c}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Status Category" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="Apttus__Status_Category__c" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!agmt.Apttus__Status_Category__c}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Status" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="Apttus__Status__c" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!agmt.Apttus__Status__c}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Geographies" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="NikeSF_Geography__c" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!agmt.NikeSF_Geography__c}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Sub-Geography" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="NikeSF_Sub_Geography__c" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!agmt.NikeSF_Sub_Geography__c}"/>
</apex:column>
</apex:pageBlockTable>
</apex:pageBlock>
</td>
</tr>
</table>
<apex:pageBlock title="Debug - SOQL" id="debug">
<apex:outputText value="{!debugSoql}" />
</apex:pageBlock>
</apex:pageBlock>
</apex:form>
</apex:page>
Please take a look at utilizing the StandardSetController (Hint: Google Pagination with the StandardSetController). The SSC has several built in functions for paginating records, including but not limited to setting the page size, next, previous, first, and last functions. It should help clean up quite a bit. The only issue I can see you might have with the SSC is the ability to sort records. However, you could handle this pretty easily be reinstanciating the standardsetcontroller with sorted data whenever you perform the sort.
EtauMatt - did you ever figure this out? I am having the same issue.