Retrieve, Modify, Persist – Part 5

Advanced SOSL

We are all familiar with the saying, “with great power, comes great responsibility”. Using SOSL to perform database searches relates to this special developer power: however, this power often comes at a price. To take full advantage of SOSL, we need to understand its limits and core functionalities.

SOSL Limits

I imagine that when Salesforce created SOSL, the engineers looked at each other’s faces and realized that they had just created a mass performance destruction weapon. In an effort to protect users from such a destructive device, the engineers needed to impose limits on their creation.

I prefer to break down SOSL limits into two types:

  • Syntax Limits
  • Result Limits

Syntax Limits apply to the actual SOSL statement. For example, the length of the overall statement should be less than 20,000 characters and within that the length of your searchTerm should be less than 10,000.

Result Limits apply to the quantity of records a SOSL statement can return. By default, the maximum number of rows that can be returned is 2,000. However, you can add custom inner limits or global limits to your statement. Let’s see an example:

FIND  {oil} IN ALL FIELDS RETURNING

  Account(Id,Name), Opportunity(Id,Name)

Will return at most 2,000 rows. If you have more than a thousand Accounts and Opportunities, then SOSL will divide the results evently. Your result set will have 1,000 Account rows and 1,000 Opportunities rows.

FIND  {oil} IN ALL FIELDS RETURNING

  Account(Id,Name),

Opportunity(Id,Name)LIMIT 100

In the scenario above, we have defined a custom global limit, by using the LIMIT n clause. Our result set will return no more than 100 total rows and will split evenly between the objects. For example, it might return 50 Accounts and 50 Opportunities.

Finally, you can specify what I call inner custom limits:

FIND  {oil} IN ALL FIELDS RETURNING

  Account(Id,Name LIMIT 20),

Opportunity(Id,Name)LIMIT 100

These limits apply in the object specified. In the example above, your statement will return no more than 20 Account records, and at most 80 Opportunity records, for a total of one hundred records combined.

Dynamic SOSL

“Flexible” is a word that we often hear when developing code, especially with search engines. A common requirement is the following: “I want the search to be flexible, and allow the user to select which object to search on and what fields to retrieve”. Whether I’m using SOQL or SOSL, the immediate thought that comes to my mind when I hear “flexible” is “dynamic”.

When we talk about dynamic SOSL, or SOQL, we are merely referring to the ability to create and run a query statement on the fly utilizing Apex.This process involves two steps:

  1. Create the statement as a String of text.
  2. Call a system function to execute the query.

In step one, the statement as a String will look something like this:

String queryString = ‘FIND\’Oil*\’IN ALL FIELDS RETURNING Account(Id, Name),Opportunity(Id, Name)’;

The second step will look like this:

List<List<sObject>> results = search.query(queryString);

SOSL Functions & Clauses

The next section describes a handful of functions that will make search and development easier:

convertCurrency()

If your environment has multi-currency enabled, then you can you use the convertCurrency() method to localize the field value to the current logged-in user’s locale.

FIND  {oil} IN ALL FIELDS RETURNING
Opportunity(Name,convertCurrency(Amount))

WITH METADATA

Use it to also return the labels of the field specified per object. The response will be formatted as a JSON object:

FIND  {oil} IN ALL FIELDS RETURNING Account(Id,Name)WITH METADATA
                         = ‘LABELS’

WITH HIGHLIGHT

Use it to also return the labels of the field specified per object. The response will be formatted as a JSON object and the matching string will be surrounded by <mark></mark> tag. However, only the first 25 rows per object will have highlight marks.This clause is only available for use in SOAP and REST API.

FIND  {oil} IN ALL FIELDS RETURNING Account(Id,Name)WITH
                         HIGHLIGHT

                        

In this post, we saw advanced features of SOSL and dove into the restricting limits for this powerful tool. In our next adventure, we advance on our “Retrieve, Modify, Persist” journey and will cover techniques for modifying the records in memory.

 

 

About the Author:

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

 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn