0

I'm stuck on standard set controller pagination for my case study as it requires if statements to create a list query which i can't convert to string

here's the codes so far

<apex:page controller="Ctrl_ContactSearch" sidebar="false">
<apex:form id="err">
    <apex:messages id="err"/>
</apex:form>
<apex:form >
    <apex:pageBlock title="Search Criteria" >
        <apex:pageBlockSection id="acco" columns="4">
            <apex:panelGrid columns="4" width="200%">
                <apex:pageBlockSection >
                    <apex:outputText value="{!Acct.Name}"/>
                </apex:pageBlockSection>
                <apex:outputLabel value="Country" />
                <apex:outputLabel value="Contact Name" />
                <apex:outputLabel value="Account Type" />
                <apex:inputText value="{!Name}" />
                <apex:inputText value="{!country}" />
                <apex:inputText value="{!contacts}" />
                <apex:selectList id="countries" value="{!picklist}" size="1">
                    <apex:selectOptions value="{!Type}"/>
                </apex:selectList>
                <div align="right">
                    <apex:commandButton value="Search" action="{!searchAccount}" reRender="account-table,err" onclick="showLoadingDialog();" oncomplete="hideLoadingDialog();" />
                </div>
            </apex:panelGrid>
        </apex:pageBlockSection>
    </apex:pageBlock>
    <apex:pageBlock title="Search Results"  id="account-table" >
        <apex:pageBlockSection rendered="{!isRenderedNoResult}">
            <i>{!NoResultsFound}</i>
        </apex:pageBlockSection>
        <apex:pageBlockSection columns="1" >
            <apex:pageBlockTable value="{!acc}" var="c" rendered="{!isRendered}">
                <apex:column headerValue="{!$ObjectType.Account.fields.Name.Label}">
                    {!c.Account.Name}
                </apex:column>
                <apex:column >
                    <apex:facet name="header">Billing Country</apex:facet>
                    {!c.Account.BillingCountry}
                </apex:column>
                <apex:column >
                    <apex:facet name="header">Shipping Country</apex:facet>
                    {!c.Account.ShippingCountry}
                </apex:column>    
                <apex:column >
                    <apex:facet name="header">Contact Name</apex:facet>
                    {!c.Name}
                </apex:column>
                <apex:column >
                    <apex:facet name="header">Types</apex:facet>
                    {!c.Account.Type}
                </apex:column>                                                       
                <apex:column headerValue="Year Created">
                    <apex:outputText value="{0,date,yyyy}">
                        <apex:param value="{!c.Account.CreatedDate}" />
                    </apex:outputText>
                </apex:column>
            </apex:pageBlockTable>
             <apex:panelGrid columns="4">                
            <apex:commandButton status="fetchStatus" reRender="searchBlock" value="<<" action="{!setCon.first}" disabled="{!!setCon.hasPrevious}" title="First Page"/>
            <apex:commandButton status="fetchStatus" reRender="searchBlock" value="<" action="{!setCon.previous}" disabled="{!!setCon.hasPrevious}" title="Previous Page"/>
            <apex:commandButton status="fetchStatus" reRender="searchBlock" value=">" action="{!setCon.next}" disabled="{!!setCon.hasNext}" title="Next Page"/>
            <apex:commandButton status="fetchStatus" reRender="searchBlock" value=">>" action="{!setCon.last}" disabled="{!!setCon.hasNext}" title="Last Page"/>                 
            <apex:outputPanel >                      
            </apex:outputPanel>
        </apex:panelGrid> 
        </apex:pageBlockSection>  
    </apex:pageBlock>
</apex:form>
<script>
var label = document.getElementById("accountLabel");
</script>    
</apex:page>

and the controller class

public class Ctrl_ContactSearch {
public Account acct { get; set; }
public List<Contact> acc { get; set; }
public String name { get; set; }
public String country { get; set; }
public String contacts { get; set; }
public string pick{set;get;}
public string picklist{set;get;}   
public string NoResultsFound {set; get;}
public boolean isRendered {set; get;}
public boolean isRenderedNoResult {set; get;}   

transient List<Schema.PicklistEntry> ple = new List<Schema.PicklistEntry>();
public List<SelectOption> getType(){
    List<SelectOption> options = new List<SelectOption>();
    Schema.DescribeFieldResult fieldResult = Account.Type.getDescribe();
    ple = fieldResult.getPicklistValues();
    options.add(new SelectOption('--', '--'));
    for( Schema.PicklistEntry f : ple){
        options.add(new SelectOption(f.getLabel(), f.getValue()));
    }    
    return options; 
}       
public Ctrl_ContactSearch(){
    acc = new List<Contact>();
    acct = new Account();
    isRenderedNoResult = true;    
    NoResultsFound = 'No matching records.';
}    
public PageReference searchAccount(){ 
    isRendered = true;
    isRenderedNoResult = false;
    name = name.replace('*','%');  
    if(country.length()!=0){
        country='%'+country+'%';
    }
    if(name.length()>=2){           
        if(country.length()!=0){
            if(contacts.length()!=0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) and account.Type= :picklist and Name = :contacts order by account.name asc];
                }
                if(picklist.length()==2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) and Name = :contacts order by account.name asc];
                }
            }
            if (contacts.length()==0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) and account.Type= :picklist order by account.name asc];
                }
                if(picklist.length()==2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) order by account.name asc];
                }
            }
        }
        if(country.length()==0){
            if(contacts.length()!=0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and Name = :contacts and account.Type= :picklist order by account.name asc];
                }
                if(picklist.length()==2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and Name = :contacts order by account.name asc];
                }
            }
            if(contacts.length()==0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name and account.Type= :picklist order by account.name asc];
                }
                if(picklist.length()==2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Name LIKE :name order by account.name asc];
                }
            }
        }
    }
    if(name.length()==0){
        if(country.length()!=0){
            if(contacts.length()!=0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) and account.Type= :picklist and Name = :contacts order by account.name asc];
                }
                if(picklist.length()==2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where(account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) and Name = :contacts order by account.name asc];
                }
            }
            if (contacts.length()==0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) and account.Type= :picklist order by account.name asc];
                }
                if(picklist.length()==2){ 
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where (account.BillingCountry LIKE :country or account.ShippingCountry LIKE :country) order by account.name asc];
                } 
            }
        }
        if(country.length()==0){
            if(contacts.length()!=0){
                if(picklist.length()!=2){ 
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where Name = :contacts and account.Type= :picklist order by account.name asc];
                } 
                if(picklist.length()==2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where Name = :contacts order by account.name asc ];
                }
            }
            if(contacts.length()==0){
                if(picklist.length()!=2){
                    acc = [select account.id, account.name, account.type, account.billingcountry, account.shippingcountry, name, account.createddate from contact
                           where account.Type= :picklist 
                           order by account.name asc];
                }
                if(picklist.length()==2){                       
                    ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'Please provide a value for at least 1 of the available criteria.');
                    ApexPages.addMessage(myMsg);
                    isRendered = false;
                    isRenderedNoResult = true; 
                }
            }
        }
    }
    if(name.length()==1){
        ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'A minimum of 2 characters is required to search using the Account Name.');
        ApexPages.addMessage(myMsg);
        isRendered = false;
        isRenderedNoResult = true; 

    }
    if(acc.size()==0){
        isRendered = false;
        isRenderedNoResult = true; 
    }        
    return null;
    }
    }
2
  • I'd suggest you read up on Dynamic SOQL. However, you do not need Dynamic SOQL to use a standard set controller. SFSE can provide you with more assistance if you can make your question specific and detailed; it's not totally clear what you need here.
    – David Reed
    Dec 25 '18 at 1:58
  • 1
    Please do not blank out your questions once you've received an answer. By doing so you eliminate the usefulness of the question and answer to the community.
    – David Reed
    Dec 25 '18 at 12:16
2

ApexPages.StandardSetController does not require the use of Dynamic SOQL. Here's an example from the documentation for that class (linked above):

ApexPages.StandardSetController ssc = 
new ApexPages.StandardSetController(Database.getQueryLocator([SELECT Name,CloseDate FROM Opportunity]));

You can pass your static SOQL query directly to Database.getQueryLocator() when you initialize the StandardSetController.

Your use case does seem appealing for the use of Dynamic SOQL. Dynamic SOQL allows you to construct your queries in the form of a string, including by using templating with String.format(). It's a powerful (but sometimes very tricky) tool, and it's important to note that values you use in Dynamic SOQL need to be escaped to prevent SOQL injection attacks by using String.escapeSingleQuotes() or by using simple Apex binds, as you currently do.

Before going down that road, I'd work on your logical structure. Rather than using enormously complex chains of if and else statements on the lengths of various variables (the details of which I have not spent the time to parse out), I would recommend you establish Boolean variables and try as best you can to flatten your logic.

It might look something like this:

Boolean hasCountry = String.isNotBlank(country);
Boolean hasContacts = String.isNotBlank(contacts);
Boolean hasPicklist = String.isNotBlank(picklist);
// or whatever criteria you need to establish semantics of the user input.

Then think through a truth table: how many different combinations of these true/false situations are there? Do any of them reduce to the same behavior?

If you stick with static SOQL, write your logic out using semantic statements about what information you have, rather than what your variables look like:

if (hasCountry && hasContacts && hasPicklist) {
     // do one kind of query.
}
if (hasCountry && hasContacts && !hasPicklist) {
    // do another
}
if (hasCountry && !hasContacts && hasPicklist) {
    // yet a third
} 

If you switch to Dynamic SOQL, you may be able to dramatically reduce the code's length by simply starting from a core query and appending clauses based on the values:

String query = 'SELECT STUFF_GOES_HERE FROM Contact WHERE ';
List<String> whereClauses = new List<String>();

if (hasCountry) {
     whereClause.add('Account.BillingCountry LIKE :country');
}

// And so on...

query += String.join(whereClauses, ' AND ');

Then, finally, perform a dynamic query.

(Please note that these examples aren't meant to be specific to your use case, but illustrative of some approaches to constructing the logic. I see several situations where your logic at a quick read appears to be duplicative or non-exhaustive. This represents either a bug or simply code whose branching complexity is too high to be easily understood).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.