SQL Flashcards
SOSL Limits on Search Results
The search engine limits the number of records analyzed at each stage of the search process. Sometimes, these limits cause a matching record to be excluded from a user’s results.
The search engine looks for matches to the search term across a maximum of 2,000 records (this limit starts with API version 28.0)
SOSL applies different limits for a given object or situation. If the search is for a single object, the full record limit is applied. If the search is global across multiple objects, each object has individual limits that total 2,000 records.
Admins (users with the View All Data permission) see the full set of results returned.
For all other users, SOSL applies user permission filters. Individual users see only those records that they have access to. Results sets and order vary by the user issuing the search and can change throughout the day as records are added or removed from the index.
Example
Joe Smith, a sales executive at Acme, Inc., wants to find the account record for Industrial Computing. He types Industrial into the search bar. Because so many records match the search term Industrial, a limit is imposed on the results. Unfortunately for Joe, the record he wanted is outside the limit. Because Joe used a global search, limits are applied to each object type to make up the 2,000 record limit. If Joe limited his search to just one object, the limit would apply to only that object, increasing the chance that the record he wanted would be returned. Joe retries his search by typing Industrial Computing San Francisco. With a more specific search term, the search engine is able to return better matches, even with the same limits applied.
Salesforce Object Search Language (SOSL)
Use the Salesforce Object Search Language (SOSL) to construct text-based search queries against the search index.
When building efficient SOSL queries, create filters that are selective. Search analyzes all records in the index and by default, only the top 2,000 matched records are returned based on relevancy. This API supports pagination to fetch additional matches. Sharing is applied after the result set is returned from the search stack. If your filters aren’t selective and cause search term matches of more than 2000 records, there’s a possibility of running into search crowding.
You can search text, email, and phone fields for multiple objects, including custom objects, that you have access to in a single query in the following environments.
SOAP API search() calls. See search() in the SOAP API Developer Guide.
REST API Search calls. See Search in the REST API Developer Guide.
Apex statements. See SOQL and SOSL Queries in the Apex Developer Guide.
Visualforce controllers and getter methods. See Controller Methods in the Visualforce Developer Guide.
Schema Explorer of the Eclipse Toolkit. See Schema Explorer in the Force.com IDE Developer Guide.
When to Use SOSL
Use SOSL when you don’t know which object or field the data resides in, and you want to:
Retrieve data for a specific term that you know exists within a field. Because SOSL can tokenize multiple terms within a field and build a search index from this, SOSL searches are faster and can return more relevant results.
Retrieve multiple objects and fields efficiently where the objects might or might not be related to one another.
Retrieve data for a particular division in an organization using the divisions feature.
Retrieve data that’s in Chinese, Japanese, Korean, or Thai. Morphological tokenization for CJKT terms helps ensure accurate results.
SOSL Performance Considerations
If your searches are too general, they’re slow and return too many results. Use the following clauses to define efficient text searches.
IN: Limits the types of fields to search, including email, name, or phone.
LIMIT: Specifies the maximum number of rows to return.
OFFSET: Displays the search results on multiple pages.
RETURNING: Limits the objects and fields to return.
WITH DATA CATEGORY: Specifies the data categories to return.
WITH DivisionFilter: Specifies the division field to return.
WITH NETWORK: Specifies the Experience Cloud site ID to return.
WITH PricebookId: Specifies the price book ID to return.
SOSL Syntax
A SOSL query begins with the required FIND clause. You can then add optional clauses to filter the query by object type, fields, data categories, and more. You can also determine what’s returned. For example, you can specify the order of the results and how many rows to return.
After the required FIND clause, you can add one or more optional clauses in the following order.
~~~
FIND {SearchQuery}
[ IN SearchGroup ]
[ RETURNING FieldSpec [[ toLabel(fields)] [convertCurrency(Amount)] [FORMAT()]] ]
[ WITH DivisionFilter ]
[ WITH DATA CATEGORY DataCategorySpec ]
[ WITH SNIPPET[(target_length=n)] ]
[ WITH NETWORK NetworkIdSpec ]
[ WITH PricebookId ]
[ WITH METADATA ]
[ LIMIT n ]
[ UPDATE [TRACKING], [VIEWSTAT] ]
~~~
Escaping special characters & | ! ( ) { } [ ] ^ “ ~ * ? : \ ‘ + -
Examples:
~~~
FIND {Joe Smith}
FIND {Joe Sm}
FIND {Joe Sm?th}
FIND {Joe Smith}
IN Name Fields
RETURNING lead
FIND {Joe Smith}
IN Name Fields
RETURNING lead(name, phone)
FIND {Joe Smith}
IN Name Fields
RETURNING lead (name, phone Where createddate = THIS_FISCAL_QUARTER)
FIND {“Joe Smith” OR “Joe Smythe”}
IN Name Fields
RETURNING lead(name, phone), contact(name, phone)
~~~
SOSL Syntax Details
FIND {SearchQuery}
Required. Specifies the text (words or phrases) to search for. Enclose the search query with curly braces.
If the SearchQuery string is longer than 10,000 characters, no result rows are returned. If SearchQuery is longer than 4,000 characters, any logical operators are removed. For example, the AND operator in a statement with a SearchQuery that’s 4,001 characters will default to the OR operator, which could return more results than expected.
FORMAT()
Use FORMAT with the FIND clause to apply localized formatting to standard and custom number, date, time, and currency fields. The FORMAT function supports aliasing. In addition, aliasing is required when the query includes the same field multiple times.
IN SearchGroup
Scope of fields to search. One of the following values:
ALL FIELDS
NAME FIELDS
EMAIL FIELDS
PHONE FIELDS
SIDEBAR FIELDS
If unspecified, the default is ALL FIELDS. You can specify the list of objects to search in the RETURNING FieldSpec clause.
RETURNING FieldSpec
Information to return in the search result. List of one or more objects and, within each object, list of one or more fields, with optional values to filter against. If unspecified, the search results contain the IDs of all objects found.
Example:
~~~
FIND {MyProspect} RETURNING Contact(FirstName, LastName LIMIT 20), Account(Name, Industry LIMIT 10), Opportunity LIMIT 50
~~~
USING ListView=
Optional clause used to search within a single given object’s list view. Only one list view can be specified. Only the first 2,000 records of the list view are searched, according to the sort order the user has set for the list view.
WITH SPELL_CORRECTION
When set to true, spell correction is enabled for searches that support spell correction. When set to false, spell correction isn’t enabled. The default value is true.
WITH METADATA = MetadataSpec
Optional. Specifies if metadata is returned in the response. The default setting is no, meaning no metadata is returned.
SOSL Limits on External Object Search Results
SOSL applies specific limits to external objects in search results.
To include an external object in SOSL and Salesforce searches, enable search on both the external object and the external data source. However, syncing always overwrites the external object’s search status to match the search status of the external data source.
Only text, text area, and long text area fields on external objects can be searched. If an external object has no searchable fields, searches on that object return no records.
External objects don’t support the following.
INCLUDES operator
LIKE operator
EXCLUDES operator
toLabel() function
External objects also don’t support Salesforce Knowledge-specific clauses, including the following.
UPDATE TRACKING clause
UPDATE VIEWSTAT clause
WITH DATA CATEGORY clause
**External objects must be specified explicitly in a RETURNING clause to be returned in search results. **For example:
~~~
FIND {MyProspect} RETURNING MyExternalObject, MyOtherExternalObject
~~~
Text strings must be 100 or fewer characters.
The following limits apply only to the OData 2.0 and 4.0 adapters for Salesforce Connect.
The OData adapters for Salesforce Connect don’t support logical operators in a FIND clause. We send the entire search query string to the external system as a case-sensitive single phrase after removing all ASCII punctuation characters except hyphens (-). For example, FIND {MyProspect OR “John Smith”} searches for the exact phrase “MyProspect OR John Smith”.
The following limits apply only to custom adapters for Salesforce Connect.
The convertCurrency() function isn’t supported in SOSL queries of external objects.
WITH clauses aren’t supported in SOSL queries of external objects.
