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.
SOSL: IN SearchGroup
In an SOSL query, you can specify which types of text fields to search for using the IN SearchGroup optional clause. The SearchGroup defines the scope of the search. For example, you can search for name, email, phone, sidebar, or all fields.
ALL FIELDS Search all searchable fields. If the IN clause is unspecified, then ALL FIELDS is the default setting.
EMAIL FIELDS Search only email fields.
NAME FIELDS Search only name fields for standard objects.
In addition to the Name field, Salesforce searches the following fields when using IN NAME FIELDS with these standard objects:
Account: Website, Site, NameLocal
Asset: SerialNumber
Case: SuppliedName, SuppliedCompany, Subject
Contact: AssistantName, FirstNameLocal, LastNameLocal, AccountName
Event: Subject
Lead: Company, CompanyLocal, FirstNameLocal, LastNameLocal
Note: Title
PermissionSet: Label
Report: Description
TagDefinition: NormName
Task: Subject
User: CommunityNickname
In custom objects, fields that are defined as “Name Field” are searched. In standard and custom objects, name fields have the nameField property set to true. (See the Field array of the fields parameter of the DescribeSObjectResult for more information.)
PHONE FIELDS Search only phone number fields.
SIDEBAR FIELDS Search for valid records as listed in the Sidebar dropdown list. Unlike search in the application, the asterisk (*) wildcard isn’t appended to the end of a search string.
SOSL: LIMIT n
LIMIT is an optional clause that can be added to a SOSL query to specify the maximum number of rows that are returned in the text query, which can be up to 2,000 results. If unspecified, the default is the maximum 2,000 results.
The default of 2,000 results is the largest number of rows that can be returned for API version 28.0 and later. Previous versions return up to 200 results.
The LIMIT clause can’t increase the maximum number of records returned. See SOSL Limits on Search Results.
You can set limits on individual objects or on an entire query.
When you set a limit on the entire query, results are evenly distributed among the objects returned. For example, let’s say you set an overall query limit of 20 and don’t define any limits on individual objects. If 19 of the results are accounts and 35 are contacts, then only 10 accounts and 10 contacts are returned.
FIND {test} RETURNING Account(id), Contact LIMIT 20
FIND {test} RETURNING Account(id LIMIT 20), Contact LIMIT 100
SOSL: OFFSET n
When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause in a SOSL query. For example, you can use OFFSET to display records 51 to 75 and then jump to displaying records 301 to 350. Using OFFSET is an efficient way to handle large results sets.
Use the optional OFFSET to specify the starting row offset into the result set returned by your query. Because the offset calculation is done on the server and only the result subset is returned, using OFFSET is more efficient than retrieving the full result set and then filtering the results locally. OFFSET can be used only when querying a single object. OFFSET must be the last clause specified in a query. OFFSET is available in API version 30.0 and later.
FIND {conditionExpression} RETURNING objectType(fieldList ORDER BY fieldOrderByList
LIMIT number_of_rows_to_return
OFFSET number_of_rows_to_skip)
FIND {test} RETURNING Account(id LIMIT 10 OFFSET 10)
- The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a System.SearchException: SOSL offset should be between 0 to 2000 error.
- We recommend using a LIMIT clause in combination with OFFSET if you need to retrieve subsequent subsets of the same result set. For example, you could retrieve the first 100 rows of a query using the following:
FIND {test} RETURNING Account(Name, Id ORDER BY Name LIMIT 100)
You could then retrieve the next 100 rows, 101 through 200, using the following query:
FIND {test} RETURNING Account(Name, Id ORDER BY Name LIMIT 100 OFFSET 100) - When using OFFSET, only the first batch of records will be returned for a given query. If you want to retrieve the next batch, you’ll need to re-execute the query with a higher offset value.
- Consecutive SOSL requests for the same search term but with a different OFFSET aren’t guaranteed to return a different subset of the same data if the data being searched has been updated since the previous request.
- The OFFSET clause is allowed in SOSL used in SOAP API, REST API, and Apex.
SOSL: ORDER BY
ORDER BY fieldname [ASC | DESC] [NULLS [first | last]]
FIND {MyContactName} RETURNING Contact(Name, Id ORDER BY Name), Account(Description, Id ORDER BY Description)
FIND {MyAccountName} IN NAME FIELDS RETURNING Account(Name, Id ORDER BY Name DESC NULLS last)
SOSL: RETURNING FieldSpec
RETURNING is an optional clause that you can add to a SOSL query to specify the information to be returned in the text search result.
If you don’t specify this clause, the default behavior is to return the IDs of all searchable objects in advanced search up to the maximum limit. The maximum limit is specified in the LIMITn clause or 2,000 (API version 28.0 and later), whichever is smaller. The returned IDs include custom objects even if there’s no custom tab. Search results list objects in the order specified in the clause.
External objects, articles, documents, feed comments, feed items, files, products, and solutions must be specified explicitly in a RETURNING clause to be returned in search results. For example:
FIND {MyProspect} RETURNING MySampleExternalObject, KnowledgeArticleVersion, Document, FeedComment, FeedItem, ContentVersion, Product2, Solution
RETURNING ObjectTypeName
[(FieldList [WHERE] [USING Listview=listview name] [ORDER BY Clause] [LIMIT n] [OFFSETn])]
[, ObjectTypeName [(FieldList [WHERE] [ORDER BY Clause] [LIMITn] [OFFSETn])]]
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. ListView=Recent searches for the most recently accessed items viewed or referenced by the current user.
FIND {MyProspect} RETURNING Contact(FirstName, LastName LIMIT 20), Account(Name, Industry LIMIT 10), Opportunity LIMIT 50
FIND {MyAcccount} IN ALL FIELDS RETURNING Account(Id, Name USING ListView=ListViewName)
SOQL SELECT Syntax
Unlike other SQL languages, you can’t specify * for all fields. You must specify every field you want to get explicitly. If you try to access a field you haven’t specified in the SELECT clause, you’ll get an error because the field hasn’t been retrieved.
You don’t need to specify the Id field in the query as it is always returned in Apex queries, whether it is specified in the query or not. For example: SELECT Id,Phone FROM Account and SELECT Phone FROM Account are equivalent statements. The only time you may want to specify the Id field is when it’s the only field you’re retrieving because you have to list at least one field: SELECT Id FROM Account. You may want to specify the Id field also when running a query in the Query Editor as the ID field won’t be displayed unless specified.
SOQL query syntax consists of a required SELECT statement followed by one or more optional clauses, such as TYPEOF, WHERE, WITH, GROUP BY, and ORDER BY.
The SOQL SELECT statement uses the following syntax:
~~~
SELECT fieldList [subquery][…]
[TYPEOF typeOfField whenExpression[…] elseExpression END][…]
FROM objectType[,…]
[USING SCOPE filterScope]
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)}
[HAVING havingConditionExpression] ]
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
[LIMIT numberOfRowsToReturn]
[OFFSET numberOfRowsToSkip]
[{FOR VIEW | FOR REFERENCE} ]
[UPDATE {TRACKING|VIEWSTAT} ]
[FOR UPDATE]
~~~
```
SELECT Id FROM Contact WHERE Name LIKE ‘A%’ AND MailingCity = ‘California’
SELECT Name FROM Account
ORDER BY Name DESC NULLS LAST
SELECT COUNT() FROM Contact
SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
SELECT Name, COUNT(Id) FROM Account
GROUP BY Name HAVING COUNT(Id) > 1
SELECT Contact.FirstName, Contact.Account.Name FROM Contact
SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry = ‘media’
SELECT Name, (SELECT LastName FROM Contacts) FROM Account
SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account
SELECT Name, (SELECT LastName FROM Contacts WHERE CreatedBy.Alias = ‘x’)
FROM Account WHERE Industry = ‘media’
SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c
FROM Daughter__c WHERE Mother_of_Child__r.LastName__c LIKE ‘C%’
SELECT Name, (SELECT Name FROM Line_Items__r)
FROM Merchandise__c WHERE Name LIKE ‘Acme%’
SELECT Id, Owner.Name FROM Task WHERE Owner.FirstName like ‘B%’
SELECT Id, Who.FirstName, Who.LastName
FROM Task WHERE Owner.FirstName LIKE ‘B%’
SELECT Id, What.Name FROM Event
SELECT TYPEOF What WHEN Account THEN Phone, NumberOfEmployees
WHEN Opportunity THEN Amount, CloseDate
ELSE Name, Email END FROM Event
~~~
Understanding Relationship Names
Parent-to-child and child-to-parent relationships exist between many types of objects. For example, Account is a parent of Contact.
To be able to traverse these relationships for standard objects, a relationship name is given to each relationship. The form of the name is different, depending on the direction of the relationship:
For child-to-parent relationships, the relationship name to the parent is the name of the foreign key, and there is a relationshipName property that holds the reference to the parent object. For example, the Contact child object has a child-to-parent relationship to the Account object, so the value of relationshipName in Contact is Account. These relationships are traversed by specifying the parent using dot notation in the query, for example:
SELECT Contact.FirstName, Contact.Account.Name from Contact
This query returns the first names of all the contacts in the organization, and for each contact, the account name associated with (parent of) that contact.
For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other objects, and has a relationshipName for each, Assets, Cases, and Contacts.These relationships can be traversed only in the SELECT clause, using a nested SOQL query. For example:
SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account
This query returns all accounts, and for each account, the first and last name of each contact associated with (the child of) that account.
Understanding Relationship Names, Custom Objects, and Custom Fields
When you use a relationship name in a query, you must use the relationship names without the _c. Instead, append an _r (underscore underscore r).
For example:
When you use a child-to-parent relationship, you can use dot notation:
~~~
SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c
FROM Daughter__c
WHERE Mother_of_Child__r.LastName__c LIKE ‘C%’
~~~
Parent-to-child relationship queries do not use dot notation
~~~
SELECT LastName__c,
(
SELECT LastName__c
FROM Daughters__r
)
FROM Mother__c
~~~
