How to avoid a QueryException with null SOQL queries?

Question

I've been exploring different ways to assign a SOQL query that would potentially have no results in some situations (when working correctly).

However, I can't seem to avoid the System.QueryException: List has no rows for assignment to SObject error.

Here is the relevant bit from my code:

list<User> ccUsers = new list<User>();
ccUsers.addAll([SELECT id FROM User WHERE ContactID = :joint.contact__c AND isActive = True LIMIT 1]);

• I thought that by using a List, rather than assigning the SOQL directly to the User sObject, I could avoid the error, but I was wrong.
• Then I thought that by using the list method .addAll(list<>), I could avoid the error (after all, shouldn't you be able to add an empty list to another list?), but I still get the error.

Bottom line, I don't want my code to break and stop running just because a SOQL query doesn't return any results. I just want it to return an empty list of the sObject being queried.

edit: I just ran my current code in Execute Anonymous in console, and it successfully ran without error, with CCusers being an empty list, as desired. So why am I getting Developer script exception emails with a stacktrace to the exact line of code where I'm adding a SOQL query result to a list?

Answer 1

You instead need to be assigning it directly to the list:

List<User> ccUsers = [SELECT Id FROM User WHERE ContactID = :joint.contact__c AND isActive = true LIMIT 1];

if (ccUsers.isEmpty())
{
  //Do something because we found no matching Users
} else
{
  //Do something else because there is a matching user
}

Answer 2

I would suggest something similar to @dphil, but also instanciate the list as well.

list<User> ccUsers = new list<User>([SELECT id FROM User WHERE ContactID = :joint.contact__c AND isActive = True LIMIT 1]);

Now, even if the SQOL returns no rows, you still have a list of size 0, and you won't get the query exception.

Answer 3

You can use try/catch block for this. But it is strange that you're getting error on a list.

With try catch it will look like this;

try{
  list<User> ccUsers = new list<User>();
  ccUSers = [SELECT id FROM User WHERE ContactID = :joint.contact__c AND isActive = True LIMIT 1];
   //not empty
}catch(Exception e){
    //empty list
}

Answer 4

The error which we are getting here is because the SOQL returns 0 result.

You need to make the query conditional - ie have some check prior to sending the query or the last resort can be try catch block. Although i personally feel you must go with the first option