Salesforce Object Search Language

SOSL (Salesforce Object Search Language) performs text searches in records, and queries multiple types of objects at the same time.

FIND {keyword} IN ALL FIELDS RETURNING ObjectName(returnFields);
  • FIND is the keyword to initiate a SOSL query.
  • {keyword} is the string we are looking for.
  • IN what fields are we looking in
  • ALL keyword for searching through all Objects’ fields
  • FIELDS keyword stating we are searching in fields
  • RETURNING keyword for what values we want to return
  • ObjectName(returnFields) returns the fields from Object when there is a match

Let’s run an example in the Query Editor of Developer Console to first see what our query returns. To do this, open Developer Console and select Query Editor from the bottom tab:

FIND {Test} IN ALL FIELDS RETURNING Contact(LastName, Phone, Email);

This returns a list of all contacts that have test in their last name, field or email. Let’s save this in a List of List of sObjects and then into a variable of Contact array type:

List<List<sObject>> searchList = FIND {Test} IN ALL FIELDS RETURNING Contact(LastName, Phone, Email);

Contact[] con = (Contact[])searchList[0];

In line 1, we make a List of List of sObjects to store all values that out SOSL query returns. The sObject type allows us to store virtually any standard object instead of a specific one.
In line 2, we convert our searchList into a Contact Object type. The conversion of a value from one data type to another is called Type Casting.


Type Casting

The process of changing a value from one data type to another is called Type casting. To put simply in coffee terms, if you grind your coffee beans roughly and want only the coffee dust to pass through, you put the entire mixture through a strainer and allow only coffee dust to get in the other container. The syntax is:

dataType variableName = (dataType) valueOrVariableToConvert;
  • dataType in both cases is the data type we want to convert the variable/value into
  • variableName is the name of converted variable/value
  • valueOrVariableToConvert is the variable or value we want to convert.

Let’s convert a simple Decimal into an Integer.

Decimal value1 = 45.115;
Integer value2 = (Integer) value1;
System.debug('The decimal is: '+ value1 + '. The integer is: ' + value2);

The console log would output: The decimal is: 45.115. The integer is: 45.

Type casting works within reason and can only convert comparable types, for example a String cannot be converted to an Integer even if the value of String is 45.


When an SOSL query returns multiple objects, the array is indexed in the same order as the order of Objects returned in the query.

List<List<sObject>> searchList = [FIND 'test*' IN ALL FIELDS RETURNING Account (Id, Name), Contact, Opportunity];

Account[] accList = (Account[])searchList[0];
Contact[] conList = (Contact[])searchList[1];
Opportunity[] oppList = (Opportunity[])searchList[2];

Notice how the counting always starts from 0 and not 1.

SOSL vs SOQL

SOSL SOQL
Search across multiple objects Search one object at a time
Returns a List of List with multiple objects in order Returns a single List
Queries can be run on unrelated objects Queries can be run on single or related objects only
Can run across the entire org if necessary, knowledge of exact location of data is optional The location of data must be known

Summary

From the official documentation available here, page 11

Use SOQL when

  • You know in which objects or fields the data resides.
  • You want to retrieve data from a single object or from multiple objects that are related to one another.
  • You want to count the number of records that meet specified criteria.
  • You want to sort results as part of the query.
  • You want to retrieve data from number, date, or checkbox fields.

Use SOSL when

  • You don’t know in which object or field the data resides and you want to find it in the most efficient way possible.
  • You want to retrieve multiple objects and fields efficiently, and the objects may or may not be related to one another.
  • You want to retrieve data for a particular division in an organization using the divisions feature, and you want to find it in the most efficient way possible.

Some additional considerations when using SOQL or SOSL:

  • Both SOSL search queries and SOQL WHERE filters can specify text to look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses leading wildcards or a CONTAINS term.
  • In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause may be indexed. In this situation, decompose the single query into multiple queries each with one WHERE filter and then combine the results.
  • Executing a query with a null in a WHERE filter makes it impossible to use indexing. Such queries must scan the entire database to find appropriate records. Design the data model not to rely on nulls as valid field values.
  • If dynamic values are being used for the WHERE field and null values can be passed in, don’t let the query run to determine there are no records; instead check for the nulls and avoid the query if necessary.
Day 11: Apex Triggers and Order of Execution