Handling a Query Zero Results

Question

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;
}
}

Answer 1

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];
}

Answer 2

The results of the query are blank (there's a difference between having a list with 0 rows, with 1 null row in it or the list not even existing.

List<Account> accounts = [SELECT Id, Name, LogoID__c FROM Account WHERE URL_Code__c = :accountURL LIMIT 1];

if(accounts.size() == 0) {
    account = accounts[0];
}

Answer 3

You start by initializing a list where you add your account records to, if the list size is 0 you return null else you return the first result. The problem you had was that you directly created an account and it could be null, however if you work this way your list exists, isn't null, but the contents can be empty. (also I don't think it's possible to have a return statement in your constructor)

in your case you would do this:

public class application {

    private final Account account;
    private List<Account> accounts;

    /** 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() {
       accounts = [SELECT Id, Name, LogoID__c FROM Account WHERE URL_Code__c = :accountURL];
    }

    public Account getAccount() {
        if (accounts != null && accounts.size() > 0) {
           account = accounts[0];
           return account;
       } else {
           return null;
       }
    }
}