Handling a Query Zero Results

  • Let me preface with I have used Apex only a little bit.

    I have a visual force page that will be hosted on sites publicly. I take the value of the url string and then query a custom field on the account object to return a few variables to display. The query works and returns what I need however if I get 0 results I get an error, I would expect that. I have been trying to use .size() to catch if no results but can't seem to get the syntax right. See Code below for 1 variation.

    Any help and explanation would be much appreciated. Thanks!

    public class application {
    
    private final Account account;
    
    /** This will return the original URL **/
    public String currentURL { get {return site.getOriginalUrl(); } }
    
    /** Remove any slashes **/
    public String accountURL { get; set; } {
        if(currentURL.contains('/')) accountURL = currentURL.replace('/','');
        else accountURL = currentURL;
    }
    
    public application() {
       account = [SELECT Id, Name, LogoID__c FROM Account WHERE URL_Code__c = :accountURL LIMIT 1];  
       if (account.size() == 0) {
           return null;
       } else {
           return account.get(0); 
       }
    
    }
    
    public Account getAccount() {
        return account;
    }
    
    }
    

    This is the code that worked in case others come across this.

    public class application {
    
    private final Account account;
    
    /** This will return the origina; URL **/
    public String currentURL { get {return site.getOriginalUrl(); } }
    
    /** Remove any slashes **/
    public String accountURL { get; set; } {
        if(currentURL.contains('/')) accountURL = currentURL.replace('/','');
        else accountURL = currentURL;
    }
    
    public application() {
       List<Account> accs = [SELECT Id, Name, LogoID__c FROM Account WHERE URL_Code__c = :accountURL LIMIT 1];
       if(accs != null && accs.size() > 0) {
            account = accs[0];
       } else {     
           account = null;
       }
     }
    
    public Account getAccount() {
        return account;
    }
    }
    
  • The exception is thrown because you are assigning the results of your query directly to an account, rather than a list of accounts. When you assign the results of a query directly to an instance of a variable Salesforce will throw an exception if the query returns zero rows.

    As you are using an instance of an account rather than a list you cannot use the size method to test for a valid result.

    Personally in this situation I would like to see null assigned to your account variable but that's not how the platform works.

    There are a couple of ways of working around this issue. The first is to use a try/catch to handle the flow around this. Whilst often seen on the platform it is generally considered bad practice to use exception handling to manage logic flow - in this case you are expecting the possibility that you won't return any values so this doesn't count as an exception. Anyway for completeness you will often see this;

    try{
        account = [SELECT Id, Name, LogoID__c FROM Account WHERE URL_Code__c = :accountURL LIMIT 1];
    } catch(Exception ex){
        account = null;
    }
    

    A better way to perform this is to assign the results of the query to a list, test the size of the list and react as necessary. Like so:

    Account acct = null;
    List<Account> accs = [SELECT Id, Name, LogoID__c FROM Account WHERE URL_Code__c = :accountURL LIMIT 1];
    if(!accs.isEmpty()){
        acct = accs[0];
    }
    

    That was a excellent summary of the issue, thank you.

    Great explanation and summary of this issue, helped me solved a similar issue. Thanks

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used