RETRIEVE, MODIFY, PERSIST – PART 4

SOSL Basics

As opposed to SOQL, SOSL was specifically designed by Salesforce for developers who know what they want to retrieve, but do not know where it might be found. SOSL stands for Salesforce Object Search Language and it’s the query language Salesforce executes anytime a user calls the global search functionality.

In most cases, asking these two simple questions will help you identify whether you need to use SOQL or SOSL:

  1. Might the data I’m searching for reside in different fields of an object?
  2. Might my search results span multiple objects that do not relate to each other?

If you answered “Yes” to either or both of the questions above, then your business requirement is a perfect candidate for SOSL. There are other scenarios where you would want to use SOSL over SOQL, like retrieving data that is in Chinese, Japanese, Korean or Thai; or searching data within specific Division or Data Categories, among others.

SOSL Syntax

In theory, SOSL syntax is far more easy than SOQL’s. It only has one required clause: “FIND” and then you may add optional clauses as needed. Here is the most basic SOSL statement of all:

FIND   {searchTerm}

Yes, that’s it.
FIND is the only mandatory keyword; “searchTerm” is a placeholder for your search values, and the surrounding brackets are required if using the Force.com API, but not when using Apex.

Let’s say you want to retrieve any record that has the word “Oil” in your org. Here is how the SOLS statement would look:

FIND   {Oil}

We can use the Developer Console to run SOSL statements as well, like this:

The previous SOSL statement did not define where (which objects or fields) to look for the word “Oil”, but if we take a closer look at what the developer console returned, we see that:

  • A total of 20 rows were returned:
    • Three Account records
    • Ten Opportunity records
    • Seven Contact records

By default, if you do not specify any other clause,
SOSL will perform a search in all fields of all objects.

However, performing a search like this in a real Org, with thousands or even millions of records,  will be brutal when it comes to performance and is likely to hit governor limits.

SOSL & Performance

To get better performance while executing SOSL, add additional clauses to draw boundaries around your search scope and limit the size of your results. Here is a typical SOSL statement:

FIND   {searchTerm} IN searchGroup

RETURNING   sObject(fields) LIMIT N

All the red-colored words are required keywords for each clause, while the green-colored words are placeholder values.

  • {searchTerm} defines the text you want to search for. This may be as simple as a word like “Oil”, or it can also be a phrase, contain wildcards, operators and groupings. The following are examples of valid statements:
    • FIND {Oil}
    • FIND {Gas*}
    • FIND {Oil AND Gasoline}
    • FIND {Oil OR “Fossil prices”}
    • FIND {Oil OR (“Fossil prices” AND Gasoline)}
  • IN searchGroup is where you can define the fields you want to search within, but unlike SOQL, you can only use one of the following predefined options:
    • ALL FIELDS
    • EMAIL FIELDS
    • NAME FIELDS
    • PHONE FIELDS
    • SIDEBAR FIELDS
  • RETURNING sObject(fields)lets you define which specific object to search in and what fields to return if a match is found.
  • LIMIT N defines a global limit of records to return from your search. This value cannot exceed 2,000 records.

Let’s see it in action:
Say you want to look for Opportunities, Accounts, Contacts and Leads that contain either the word “Oil” or “Dickenson”. This is how it would look using SOSL:

FIND   {Oil OR Dickerson} IN ALL FIELDS
RETURNING   Account(Name,Website), Contact(Name,Account.Name),
Opportunity(Name,Account.Name,Amount),
Lead(Name,Company,Title,Email)
LIMIT 100

The “RETURNING” clause is the longest, but it’s actually very simple to understand. Just think of it as a List of the Objects accompanied by the fields you want to retrieve if there is a match for that object. Or more specifically:

Account(Name, Website)

means “If there is any account that matches the search criteria, return the Name and Website field values”.

One important distinction is that (as shown above) you can define different fields to return for each object you are searching on. The next picture shows the results of the SOSL statement in the developer console:

Managing SOSL results in Apex

Up until this point, all of our examples have been executed in the “Query Editor” of the developer console which takes care of displaying the results nicely. When it comes to Apex, we need to do more of the work ourselves to handle the query results.

If you were to perform the previous query statement in APEX, it would look like this:

In Step 1, you perform the actual SOSL statement call. Notice the following:

  1. The result type is a List of Lists.
  2. The entire statement is enclosed by square brackets.
  3. The searchTerm is surrounded by single quotes instead of brackets.

In Step 2, you handle the results:

  1. The searchResults variable will contain a separate list for each object you specified in the “RETURNING” clause.
  2. searchResults will maintain the same order as the order you specify the objects in the the “RETURNING” clause.
  3. From here on, you can do a LIST FOR LOOP to process each records.

In this post, we got to understand the basics of SOSL.  Stay tuned for Part 5, where we will  cover advanced features, techniques and Governor Limits.

 

 

 

About the Author:

Clara Perez is a Salesforce MVP and Lead Developer at Great Wave who loves teaching Salesforce concepts.