Bulkify your code by combining SOQL queries

Preface: this post is part of the Bulkify Your Code series.

Best advice I’ll ever give you: never ever have a SOQL query inside a loop!

Having a SOQL query inside a loop is a major breach of the unwritten code of Apex developers. It’s like wearing socks and sandals at the same time. Don’t ever wear socks with sandals, trust me on this one!

Why can’t we have SOQL queries inside loops?
Simple. The #1 most common governor limit restricts you to 100 SOQL queries. Loops, especially those iterating over Trigger.new, will easily run 200+ queries and push you across the limit.

How do we avoid using SOQL queries inside loops?
Combine your queries into one! There’s always a way to do this for any trigger you write!
Let’s look at this chapter’s trigger, which can potentially run 200 SOQL queries, and combine them.

How to combine SOQL queries:

1. Create a Set of all potential values you need to query against.
   – In this chapter’s trigger, this means every possible value of newCase.SuppliedEmail
2. Query against all records needed in Step 1 using a single SOQL query
   – Always do this before entering the loop!

Now we apply the above steps to this chapter’s trigger:

trigger FixCreator on Case (before insert) {

  // Step 1: Create a set of all values to query
  Set<String> allEmails = new Set<String>();
  for (Case newCase : Trigger.new) {
    if (newCase.SuppliedEmail != null) {
      allEmails.add(newCase.SuppliedEmail);
    }
  }

  // Step 2: Query for all the records in Step 1
  List<User> potentialUsers = [SELECT Id, Email FROM User
                                WHERE Email IN :allEmails];
  // Step 3: Associate each record with the correct query result
  for (Case newCase : Trigger.new) {
    // We'll cover this in the next post!
  }

}

Now that we’ve combined our queries into one, we no longer have to worry about hitting the 100 SOQL query governor limit.

We have a new problem now however. Before combined queries, it was easy associating the correct Case creator to each Case because we simply did a brand new SOQL query and took a single result. Now however with combined queries, we have to choose the correct Case creator for each Case out of our combined list of many potential creators!

We’ll solve this dilemma and close the book on this chapter in the next post!

Next post: Use Maps to navigate across Lists!