SOSL Query in Salesforce Apex
SOSL Query Salesforce Apex

SOSL Query in Salesforce Apex

On January 26, 2024, Posted by , In Salesforce Apex Tutorial, With Comments Off on SOSL Query in Salesforce Apex
SOSL Query Salesforce Apex
SOSL Query in Salesforce Apex

Table of Contents

What is SOSL?

Salesforce Object Search Language (SOSL) is used to perform text searches in Salesforce records. Using SOSL, you can search fields across multiple standard and custom objects in Salesforce.

A SOSL query returns a list of lists of sObjects, where each list contains the results from the search for a particular sObject type. A SOSL query always returns results in the same order that was specified in the SOSL query. Whenever a SOSL query does not return any record for a specified sObject type, it is returned as an empty list in the search results.

  1. Purpose and Efficiency: SOSL is designed to help developers quickly find specific phrases or words across multiple objects in Salesforce, primarily searching within name, phone, and email fields to streamline data retrieval.
  2. Focus on Object Level: Unlike other search tools that may target single objects, SOSL queries multiple objects at the object level, returning results in a structured format that is both predictable and easy to manage.
  3. Result Structure: The results from a SOSL query are presented as a “list of lists” of sObjects, where each list corresponds to a specific sObject type included in the query, maintaining the order in which they were searched.
  4. Handling of No Results: In cases where a SOSL query finds no records for a specified sObject type, it provides an empty list for that type, ensuring consistent result structure and allowing developers to easily handle scenarios with no data.

How to write SOSL query?

1. Basic Query Structure

A basic SOSL query consists of the FIND clause, which specifies the text expression to search for, and the IN clause, which specifies the fields to search within. The RETURNING clause specifies the object(s) and fields from which to retrieve data.

Example:

FIND {John} 
IN Name Fields 
RETURNING Contact(Id, Name, Email), Account(Id, Name)

This query searches for the term “John” in the Name fields of both Contact and Account objects, and it retrieves the Id, Name, and Email of contacts and Id and Name of accounts.

2. Using Wildcards

You can use wildcards to broaden your search. The asterisk (*) represents zero or more characters, and the question mark (?) represents exactly one character.

Example:

FIND {Sm?th*} 
IN Name Fields 
RETURNING Account(Id, Name)

This searches for names like ‘Smith’, ‘Smyth’, etc., followed by any sequence of characters.

3. Searching in All Fields

If you want to search all text fields, use ALL FIELDS.

Example:

FIND {Acme} 
IN ALL FIELDS 
RETURNING Account(Id, Name), Contact(Id, Name)

This query searches all text fields for “Acme” in both Account and Contact objects.

4. Specifying Conditions and Limits

You can add conditions and limits to the RETURNING clause to refine your results.

Example:

FIND {Joe} 
IN Name Fields 
RETURNING Contact(Id, Name WHERE LastName='Bloggs' AND Department='Sales' LIMIT 10)

This searches for “Joe” in Contact names but only where the last name is “Bloggs” and the department is “Sales”. It limits the results to 10 records.

5. Dynamic SOSL

You can also construct SOSL queries dynamically in Apex code, which is useful for building search functionalities in applications.

Example:

String searchQuery = 'FIND \'*' + searchString + '*\' IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)';
List<List<SObject>> searchList = Search.query(searchQuery);

In this Apex code example, searchString is a variable holding the user input which is included in the SOSL query.

Best Practices

  • Use SOSL when you need to search across multiple objects or when you don’t know which object or field the data resides in.
  • SOSL can return a large number of records; make sure to use WHERE and LIMIT clauses to narrow down the results and prevent performance issues.
  • Remember that SOSL respects field-level security. Ensure your application handles scenarios where users may not have permission to see all search results.

Difference between SOQL and SOSL

SOQL (Salesforce Object Query Language)SOSL (Salesforce Object Search Language)
Used for querying data from specific objects and their related objects.Used for performing text searches in records across multiple objects.
Allows querying of individual records similar to SQL SELECT statements.Allows searching of fields with a search expression using keywords.
Returns records that meet specific criteria defined in the WHERE clause.Returns records containing the search term, potentially from multiple object types simultaneously.
Query targets a specific object or a specific set of objects defined in the FROM clause.Searches the global index of all records that meet the search criteria across multiple objects.
Supports nested queries and relationship queries.Limited to simple search conditions; does not allow nested queries or relationship queries.
Ideal for precise, complex queries where you know the structure of the database.Ideal for broad, text-based searches where the exact location of data is unknown.
Can include COUNT, SUM, MAX, MIN, and other functions.Primarily focuses on finding text in record fields without aggregate functions.
Has a row limit based on context, usually 50,000 records in batch Apex.Has a different limit, typically 2,000 records for global search across objects.
Difference between SOQL and SOSL

SOSL Syntax

SOSL (Salesforce Object Search Language) syntax allows developers to construct queries that search across multiple objects efficiently. Here’s an overview of the basic syntax and an example to demonstrate how it works:

Basic Syntax of SOSL:

FIND {searchQuery} IN {searchGroup} RETURNING {objectType}(fieldList [WHERE condition] [ORDER BY fieldList] [LIMIT n])
  • {searchQuery}: The text phrase or word you want to search for, enclosed in single quotes.
  • {searchGroup}: Specifies where to perform the search, such as in ALL FIELDS, NAME FIELDS, EMAIL FIELDS, or PHONE FIELDS.
  • {objectType}: The type of object to retrieve, like Account, Contact, etc.
  • fieldList: Specifies the fields to retrieve from the search results.
  • WHERE condition (optional): Adds a condition to filter results.
  • ORDER BY fieldList (optional): Specifies the order of the returned records.
  • LIMIT n (optional): Limits the number of results returned.

Example of a SOSL Query:

FIND 'Acme*' IN NAME FIELDS RETURNING Account(Id, Name WHERE Name LIKE 'Acme%' ORDER BY Name LIMIT 10), Contact(Id, LastName, FirstName WHERE LastName LIKE 'Acme%')

In this example:

  • *FIND ‘Acme‘**: Searches for records that start with “Acme”.
  • IN NAME FIELDS: Limits the search to name fields across objects.
  • RETURNING: Specifies that results should include records from the Account and Contact sObjects.
    • For Account, retrieves the Id and Name where the name starts with “Acme”, sorts the results by name, and limits to 10 records.
    • For Contact, retrieves the Id, LastName, and FirstName where the last name starts with “Acme”.

Let’s take an example that searches the text ‘Salesforce’ in Account, Contact, and opportunity fields.

List<List<sObject>> searchList = [FIND 'Salesforce' IN ALL FIELDS 
 RETURNING Account(Name,Industry WHERE Industry='Chemical' LIMIT 1),Contact(FirstName,LastName),Opportunity(Name)];

Banking & Finance SOSL Examples

1. Searching for Customer Accounts with Bank Services

This SOSL query searches for customer accounts with names containing the keywords ‘Savings‘ or ‘Checking‘. The results are filtered to include only those accounts within the ‘Banking‘ industry. The retrieved accounts are then iterated over, and their names and industries are printed to the debug log. This helps bank employees quickly locate customer accounts that utilize specific banking services.

List<List<SObject>> searchList = [FIND 'Savings OR Checking' IN NAME FIELDS RETURNING Account(Id, Name, Industry WHERE Industry = 'Banking')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Account acc = (Account) sobj;
        System.debug('Account Name: ' + acc.Name + ', Industry: ' + acc.Industry);
    }
}

2. Searching for Bank Employees by Role

This query targets contacts with job titles including ‘Loan Officer‘ or ‘Branch Manager‘. By specifying these keywords in the search, the query ensures that only relevant contacts are returned. The code then iterates through the results, printing the contacts’ first names, last names, and titles to the debug log. This is useful for identifying key bank employees in different roles.

List<List<SObject>> searchList = [FIND 'Loan Officer OR "Branch Manager"' IN ALL FIELDS RETURNING Contact(Id, FirstName, LastName, Title WHERE Title LIKE '%Loan Officer%' OR Title LIKE '%Branch Manager%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Contact con = (Contact) sobj;
        System.debug('Contact Name: ' + con.FirstName + ' ' + con.LastName + ', Title: ' + con.Title);
    }
}

3. Searching for Loan Opportunities

This example demonstrates searching for loan-related opportunities using keywords such as ‘Personal Loan‘ and ‘Business Loan‘. The query looks through all fields for these terms and returns opportunities where the description contains them. The retrieved opportunities’ names, stages, and amounts are then logged for review, helping track potential revenue from loan products.

List<List<SObject>> searchList = [FIND 'Personal Loan OR Business Loan' IN ALL FIELDS RETURNING Opportunity(Id, Name, StageName, Amount WHERE Description LIKE '%Personal Loan%' OR Description LIKE '%Business Loan%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Opportunity opp = (Opportunity) sobj;
        System.debug('Opportunity Name: ' + opp.Name + ', Stage: ' + opp.StageName + ', Amount: ' + opp.Amount);
    }
}

4. Searching for Potential Investment Leads

This SOSL query searches for leads interested in investment services with keywords like ‘Investment Portfolio‘ or ‘Wealth Management‘. The results are filtered to include leads whose descriptions contain these terms. The code processes the results and logs the lead’s first name, last name, company, and description. This aids in identifying prospective clients interested in investment banking services.

List<List<SObject>> searchList = [FIND 'Investment Portfolio OR Wealth Management' IN ALL FIELDS RETURNING Lead(Id, FirstName, LastName, Company, Description WHERE Description LIKE '%Investment Portfolio%' OR Description LIKE '%Wealth Management%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Lead lead = (Lead) sobj;
        System.debug('Lead Name: ' + lead.FirstName + ' ' + lead.LastName + ', Company: ' + lead.Company + ', Description: ' + lead.Description);
    }
}

5. Searching for Fraud-Related Cases

This query searches for cases with subjects containing the keywords ‘Fraudulent Activity’ or ‘Unauthorized Transaction’. The query results are filtered to include only those cases matching the specified criteria. The retrieved cases’ numbers, subjects, and statuses are then printed to the debug log, providing insight into prevalent fraud issues. This helps bank employees manage and resolve fraud-related cases efficiently.

List<List<SObject>> searchList = [FIND 'Fraudulent Activity OR "Unauthorized Transaction"' IN ALL FIELDS RETURNING Case(Id, CaseNumber, Subject, Status WHERE Subject LIKE '%Fraudulent Activity%' OR Subject LIKE '%Unauthorized Transaction%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Case cse = (Case) sobj;
        System.debug('Case Number: ' + cse.CaseNumber + ', Subject: ' + cse.Subject + ', Status: ' + cse.Status);
    }
}

Healthcare SOSL Examples

1. Searching for Patient Records by Condition

This SOSL query searches for patient records that mention specific medical conditions such as ‘Diabetes’ or ‘Hypertension’. The results include patients whose medical history or notes contain these keywords. The code iterates through the results, printing the patients’ names and conditions to the debug log. This helps healthcare providers quickly identify and manage patients with specific health conditions.

List<List<SObject>> searchList = [FIND 'Diabetes OR Hypertension' IN ALL FIELDS RETURNING Patient__c(Id, Name, Medical_History__c WHERE Medical_History__c LIKE '%Diabetes%' OR Medical_History__c LIKE '%Hypertension%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Patient__c patient = (Patient__c) sobj;
        System.debug('Patient Name: ' + patient.Name + ', Medical History: ' + patient.Medical_History__c);
    }
}

2. Searching for Upcoming Medical Appointments:

This SOSL query searches for upcoming medical appointments that are scheduled within a certain date range, using keywords like ‘Check-up’ or ‘Follow-up’. The query returns appointments where the description or type matches these terms. The code processes the results and logs the appointment details, including patient names, appointment dates, and types. This is useful for healthcare providers to track and manage upcoming appointments efficiently.

List<List<SObject>> searchList = [FIND 'Check-up OR Follow-up' IN ALL FIELDS RETURNING Appointment__c(Id, Patient_Name__c, Appointment_Date__c, Appointment_Type__c WHERE Appointment_Date__c > TODAY)];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Appointment__c appointment = (Appointment__c) sobj;
        System.debug('Patient Name: ' + appointment.Patient_Name__c + ', Appointment Date: ' + appointment.Appointment_Date__c + ', Appointment Type: ' + appointment.Appointment_Type__c);
    }
}

Retail Applications Examples

1. Searching for Customer Orders by Product Name:

This SOSL query searches for customer orders that mention specific product names such as ‘Laptop’ or ‘Smartphone’. The results include orders where the product name or description contains these keywords. The code iterates through the results, printing the order IDs, customer names, and product names to the debug log. This helps retail staff quickly identify and manage orders containing specific products.

List<List<SObject>> searchList = [FIND 'Laptop OR Smartphone' IN ALL FIELDS RETURNING Order__c(Id, Customer_Name__c, Product_Name__c WHERE Product_Name__c LIKE '%Laptop%' OR Product_Name__c LIKE '%Smartphone%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Order__c order = (Order__c) sobj;
        System.debug('Order ID: ' + order.Id + ', Customer Name: ' + order.Customer_Name__c + ', Product Name: ' + order.Product_Name__c);
    }
}

2. Searching for Inventory Levels by Product Category:

This SOSL query searches for product inventories that fall under specific categories such as ‘Electronics’ or ‘Home Appliances’. The results include inventory records where the product category contains these keywords. The code processes the results and logs the product names, categories, and inventory levels. This helps retail managers track and manage inventory levels for different product categories efficiently.

List<List<SObject>> searchList = [FIND 'Electronics OR "Home Appliances"' IN ALL FIELDS RETURNING Inventory__c(Id, Product_Name__c, Category__c, Inventory_Level__c WHERE Category__c LIKE '%Electronics%' OR Category__c LIKE '%Home Appliances%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Inventory__c inventory = (Inventory__c) sobj;
        System.debug('Product Name: ' + inventory.Product_Name__c + ', Category: ' + inventory.Category__c + ', Inventory Level: ' + inventory.Inventory_Level__c);
    }
}

Telecom SOSL Examples

Searching for Customer Records by Service Plan:

This SOSL query searches for customer records that mention specific service plans such as ‘Unlimited Data‘ or ‘Family Plan‘. The results include customers whose service plan descriptions contain these keywords. The code iterates through the results, printing the customer names and their service plans to the debug log. This helps telecom providers quickly locate customers who are subscribed to particular service plans.

List<List<SObject>> searchList = [FIND 'Unlimited Data OR "Family Plan"' IN ALL FIELDS RETURNING Customer__c(Id, Name, Service_Plan__c WHERE Service_Plan__c LIKE '%Unlimited Data%' OR Service_Plan__c LIKE '%Family Plan%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Customer__c customer = (Customer__c) sobj;
        System.debug('Customer Name: ' + customer.Name + ', Service Plan: ' + customer.Service_Plan__c);
    }
}

Searching for Support Cases Related to Network Issues:

This SOSL query searches for support cases with subjects containing keywords like ‘Network Outage’ or ‘Slow Internet’. The query results are filtered to include only those cases matching the specified criteria. The retrieved cases’ numbers, subjects, and statuses are then printed to the debug log, providing insight into prevalent network issues. This helps telecom support teams manage and resolve network-related cases efficiently.

List<List<SObject>> searchList = [FIND 'Network Outage OR "Slow Internet"' IN ALL FIELDS RETURNING Case(Id, CaseNumber, Subject, Status WHERE Subject LIKE '%Network Outage%' OR Subject LIKE '%Slow Internet%')];

for (List<SObject> lst : searchList) {
    for (SObject sobj : lst) {
        Case cse = (Case) sobj;
        System.debug('Case Number: ' + cse.CaseNumber + ', Subject: ' + cse.Subject + ', Status: ' + cse.Status);
    }
}

Frequently Asked Questions (FAQs)

What is meant by SOSL in Salesforce?

SOSL (Salesforce Object Search Language) is a language used in Salesforce to perform text searches within records. It is particularly useful for searching multiple objects and fields simultaneously. SOSL allows you to search for information within emails, phone numbers, text fields, and more, across multiple Salesforce objects, providing a way to quickly find relevant data.

Can we use SOSL in triggers?

Yes, SOSL can be used in triggers in Salesforce. However, it is essential to use it carefully to avoid performance issues, especially when dealing with large datasets. SOSL is typically used within triggers to search for data across multiple objects and fields when a specific event occurs, such as the insertion, update, or deletion of records.

What is the difference between SOQL and SAQL?

SOQL (Salesforce Object Query Language) is used to query records from Salesforce objects based on specified criteria. It is similar to SQL (Structured Query Language) and is used within Salesforce to retrieve data for a single object or multiple related objects.

SAQL (Salesforce Analytics Query Language) is used in Salesforce Einstein Analytics (Tableau CRM) to query data from datasets within the analytics platform. SAQL allows for more complex data manipulations and aggregations than SOQL and is designed specifically for use with the analytics data pipeline.

How do I run a SOSL query in developer console?

To run a SOSL query in the Salesforce Developer Console:

Open the Developer Console from your Salesforce setup menu.

Navigate to the “Query Editor” tab.

Type your SOSL query in the query editor text area. For example

FIND 'SearchTerm' IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)

Click the “Execute” button.

The results will be displayed in the lower part of the console.

What are the 2 major differences between SOQL and SOSL?

  1. Scope of Search:
    • SOQL: SOQL is used to retrieve records from a single Salesforce object or multiple related objects based on specific criteria. It is similar to SQL and focuses on structured data queries.
    • SOSL: SOSL is used to perform text searches across multiple Salesforce objects and fields simultaneously. It is designed for full-text searches and can search across various fields within multiple objects.
  2. Use Cases:
    • SOQL: SOQL is best suited for structured queries where you need to filter records based on specific field values, perform joins between related objects, and retrieve data in a structured format.
    • SOSL: SOSL is best suited for unstructured searches where you need to find records based on text within fields, such as searching for a keyword across multiple objects and fields, and is particularly useful for global searches.

Next chapter is SOQL Query and previous chapter is database methods.

← →
Comments are closed.