Given a scenario, write SOSL, SOQL, and DML statements in Apex.

22 Oct 2022

These are technical notes I compiled while studying using Focus on Force, a company that provides Salesforce training and certification prep services.

After studying this topic, you should be able to:

Define the main features and syntax of SOSL (Salesforce Object Search Language)
Describe how different clauses can be used in SOSL to search for a specific word or phrase
Define the main features and syntax of SOQL (Salesforce Object Query Language)
Describe how different clauses can be used in SOQL to return specific Salesforce data
Explain how various DML statements can be used for data manipulation in Apex

Table of Contents

SOSL
SOQL
SOQL FIELDS() Function
WHERE Clause Operators
SOQL Date Values and Literals
SOQL Relationship Queries
SOQL For Loop
DML Statements
Database Class Methods
Scenarios and Solutions

Introduction

SOSL is Salesforce Object Search Language, used to find Salesforce records
- Search across multiple objects for a text string
- Use FIND clause to search for a word or phrase, use RETURNING clause for specific information, use IN clause to specify fields to search for
- Search in name, email, phone, sidebar or all fields
- Filter search results using WHERE clause, limit the results returned using the LIMIT clause, order results by using the ORDER BY clause
- Use asterisks and question marks in search text
SOQL is Salesforce Object Query Language, used to query Salesforce records
- Construct a query that returns specific data
- Use SELECT and FROM commands to query data, use WHERE clause to filter data, use IN or NOT IN operator to filter on values
- Group query results using GROUP BY, order results using ORDER BY clause, use wildcards with LIKE operator, utilize relationships using dot notation and sub-query
- Use SOQL for loop to iterate over query records
DML is Data Manipulation Language, used to manipulate Salesforce records
- Perform data manipulation operations using statements
- Throws exception when data manipulation fails
- Standalone DML does not allow partial record processing
- Use the INSERT statement to add one or more records, use the UPDATE statement to modify one or more records, use the UPSERT statement to insert or update records, use the DELETE statement to delete one or more records, use the UNDELETE statement to restore records
- Use the MERGE statement to merge up to three records
Video Notes
- DML is read/write whereas SOQL/SOSL are read-only
- Searching in Salesforce is essentially running a SOSL query

SOSL

SOSL can be used when the object or field where the searched data resides is not known
- Multiple objects: allows searching text, email, phone fields across multiple objects simultaneously
- Specify Target: objects and **fields*8 to be searched and fields to be returned can be specified
- Find clause: FIND is used to specify the word or phrase to search for
- Returning clause: RETURNING clause can be used to specify the information to be returned
- SOSL clauses: IN, WHERE, and LIMIT can be used for specific text searches
- Return Type: SOSL queries return a list of lists of sObjects - can store the results in a List<List<SObject>> variable
SOSL IN: Using the IN clause enables specifying the types of fields to search for across single or multiple objects
- IN allows searching specific fields
- IN SearchGroup allows searching types of text fields
  - Default: All Fields - all searchable fields are searched
  - IN EMAIL FIELDS: Only Email fields are searched
  - IN NAME FIELDS: Only Name fields on standard and custom objects are searched, other fields on Lead searched as well
  - IN PHONE FIELDS: Only Phone fields are searched
  - Ex: FIND {Marcus} IN NAME FIELDS
SOSL WHERE: used to search and return records that match a specified criterial
- Ex: FIND {test} RETURNING Account (Id WHERE Rating = 'Hot')
SOSL LIMIT: specifies the maximum number of rows returned
- Default and max: 2000
- Limits can be set for each object individually, or for all objects, in which case the results will be evenly distributed among the objects returned
- Ex: FIND {test} RETURNING Account (Id LIMIT 10)
SOSL ORDER BY: specifies the order of the results returned
- ASCENDING or DESCENDING sorts possible, default: ascending
- NULLS FIRST or NULLS LAST orders null records at the beginning or end
- Ex: FIND {test} RETURNING Account(Id, Name, Rating, Type ORDER BY Id DESC);
SOSL Wildcards: used to search values that match text patterns
- *: matches zero or more characters in the middle or end of the search term
- ?: matches only one character in the middle or end of the search term
- Ex: FIND {Mi*} will return “Mike” and “Michael”
Wildcard example:

FIND {United*} IN ALL FIELDS
RETURNING Account(Id, Name, Phone WHERE Type = 'Customer - Direct' LIMIT 10),
Contact(Id, Name, Title, ORDER BY Name)

Results from a specific object can be retrieved by specifying its index value in the list of objects using array notation

List<List<sObject>> result = new List<List<sObject>>();
result = [FIND 'United' IN ALL FIELDS RETURNING Account (Id, Name, Type), Contact, Opportunity, Lead];

List<Account> accounts = ((List<Account>)result[0]);

for(Account a : accounts) {
    a.Type = 'Premium Customer';
}

update accounts;

query() method of the Search class can also be used to perform SOSL queries - it accepts a String parameter

String q = 'FIND {United} IN ALL FIELDS RETURNING Account (ID, Name, Type), Contact, Opportunity, Lead';
List<List<sObject>> results = Search.query(q);

for (List<sObject> objects : results) {
    System.debug(objects);
}

List<Account> accounts = (List<Account>)results[0];
List<Contact> contacts = (List<Contact>)results[1];
List<Opportunity> opportunities = (List<Opportunity>)results[2];
List<Lead> leads = (List<Lead>)results[3];

SOQL

SQOL can be used when the sObject where data to search in is known
- Supports use of aggregate functions: AVG(), COUNT(), COUNT_DISTINCT(), MIN(), MAX(), SUM()
- Bind Variables: Supports bindings to variables or expressions in Apex code
- SELECT: selects the specific fields that need to be returned, but note that a SOQL query in Apex always returns the ID field

LIST<Accounts> accounts = [SELECT Id, Name 
                             FROM Account 
                            WHERE Name = 'ABC'];
List<Vehicle__c> vehicles = [SELECT Name, Model__c, Year__c 
                               FROM Vehicle__c 
                              WHERE Manufacturer__c = 'Acme Motors'];
Contact con = [SELECT *
                 FROM Contact
                LIMIT 1];

SOQL WHERE: used to specify a condition expressions that includes one or more field expressions
- Recommended to always use this clause to reduce query results
- Parentheses: define the order of expression evaluation

SELECT Name
  FROM Account
 WHERE Name = 'ABC' AND Rating = 'Hot'

SOQL WITH: used to filter records based on field values
- Does not only support fields from the object specified in FROM but allows filter by other related criteria
- If WITH is specified, the query returns only records that match the filter and are visible to the user

SELECT Title
  FROM KnowledgeArticleVersion
 WHERE PublishStatus='online'
  WITH DATA CATEGORY Product__c AT Desktop__c

SQL IN (or NOT IN) can be used in a WHERE clause to filter on a set of values
- Can be used with lists of any type, such as a list of IDs, Integers or Strings
- Using a bind expression as a value allows filtering on a dynamic set of values in Apex

SELECT Id
  FROM Account
 WHERE AccountNumber IN : IntegerList

SOQL GROUP BY: used to specify a group of records to avoid iterating through individual query results
- Summary data: use with COUNT(fieldName) to allow analyzing records
- COUNT_DISTINCT(): returns number of distinct non-null values matching the query criteria

SELECT LeadSource, COUNT(Name)
  FROM Lead
 GROUP BY LeadSource

SOQL HAVING: used to filter the results by an aggregate function
- COUNT() and SUM() can be included in HAVING clause but not WHERE
- Use with GROUP BY to summarize data by a certain field

SELECT Name, Count(Id)
  FROM Account
 GROUP BY Name
HAVING Count(Id) > 1

SOQL ORDER BY: used to control order of the query results
- Default, ascending
- SORT NULL: can be ordered at the beginning or end of query results

SELECT Id, Name
  FROM Account
 ORDER BY Name ASC NULLS FIRST

SOQL Wildcards: used to search values that match text patterns
- MATCH SIMILAR: % and _ wildcards supported for the LIKE operator
- _: matches exactly one character
- %: matches zero or more characters

SELECT Id, LastName
  FROM Contact
 WHERE LastName LIKE 'Ab%'

Can use SOQL statement in Apex code:

List<Account> accounts = new List<Account>();
accounts = [SELECT Id, NName, Type, BillingCity
              FROM Account
             WHERE BillingCity = 'San Francisco'];

for (Account a: accounts) {
    a.Type = 'Special Customer';
}

update accounts;

Variables can be bound in SOQL where clause using the : binding operator

List<Account> accounts = [SELECT Id FROM Account WHERE AnnualRevenue >= 1000000];

List<Contact> contacts = [SELECT Id, Email FROM Contact WHERE AccountId IN :accounts];
System.debug(contacts.size())

Query strings can be constructed dynamically and executed at run time using Database class query method
- Note syntax errors will only be detected during run time, unlike static queries

Boolean isIncludeBirthdate = false;
String selectFields = 'FirstName, LastName, Email';
if (isIncludeBBirthdate == true) {
    selectedFields += ', Birthdate';
}

String whereFilter = 'WHERE Email != NULL';
String query = 'SELECT ' + selectFields + ' FROM Contact ' + whereFilter;

List<Contact> contacts = Database.query(query);

Assigning SOQL Query Results
- Single sObject can be used to store the result of a SOQL query
  - System.QueryException thrown when the query fails to exactly return a single record

SOQL FIELDS() Function

FIELDS() function can be used in a SOQL statement to query records without specifying field names of the object. Three types:
- FIELDS(STANDARD): standard fields
- FIELDS(CUSTOM): custom fields
- FIELDS(ALL): all fields

SELECT FIELDS(STANDARD)
  FROM Contact

WHERE Clause Operators

=, >, <, !=, >=, <= - all work as you would expect
LIKE, Ex: SELECT Id FROM Account WHERE Name LIKE '%Acme%'; returns ‘Acme Corp.’, ‘United Acme’, etc
IN/NOT IN, Ex: SELECT Id, FROM Account WHERE Name NOT IN ('Acme', 'Universal', 'Cosmic');
INCLUDES/EXCLUDES
- For matching values multi-select picklists only
  - Ex: ('RED;BLUE') both RED and BLUE
  - Ex: ('RED','BLUE') either RED or BLUE
  - Ex: ('RED;BLUE','GREEN') either both RED and BLUE, or GREEN
AND, OR, NOT - work as you would expect
- Ex: SELECT Id FROM Account WHERE Name != 'Acme' OR NOT AnnualRevenue <= 1000000;

SOQL Date Values and Literals

Date and DateTime field values can be used in SQL queries to filter returned data
- Use format YYYY-MM-DD (no quotes)
  - Ex: WHERE Birthdate = 1980-12-06
- DateTime fields use Coordinated Universal Time (UTC), offsetting may be required to adjust the timezone
  - Ex: WHERE CreatedDate < 2021-01-01T00:00:00+8:00
- Date Literals represent a relative range of time
  - YESTERDAY ex: WHERE CreatedDate = YESTERDAY
  - TOMORROW
  - NEXT_WEEK ex: WHERE Birthdate = NEXT_WEEK
  - LAST_90_DAYS ex: WHERE CloseDate > LAST_90_Days
  - NEXT_N_MONTHS ex: WHERE TargetDate__c > NEXT_N_MONTHS:6

SOQL Relationship Queries

Relationship queries, based on lookup and master-detail relationships enable retrieving data from related records
- SOQL relationship queries traverse both parent-to-child and child-to-parent relationships between objects to filter and return results
- Objects of one type can be returned based on criteria that applies to logic of another type
Child-to-Parent query
- Specified directly in the SELECT, FROM, or WHERE clauses using the . operator
- Ex: SELECT Id, Name, Account.Name FROM Contact WHERE Account.Rating = 'Cold'
Parent-to-Child query
- Specified using a sub-query enclosed on parentheses
- Use the plural form of the child object’s name. Ex: Contacts, OpportunityLineItems
- Ex: SELECT Name, (SELECT LastName FROM Contacts) FROM Account
- Relationship name for a custom object used in a query must be appended with __r instead of __c
  - Ex: Shipments__r and Agreements__r
Subquery example: Relationship between the Mother and Child object is called Children:
- SELECT Id, Name, (SELECT Id, Name FROM Children__r WHERE Name LIKE 'Ted%') FROM Mother__c
Filter example: returns the name of all accounts whose Type is “Customer” and for each account returned, the first and last name of the related contacts created by “Martin Gessner”

SELECT Name, (SELECT FirstName, LastName
                FROM Contacts
               WHERE CreatedBy.Name = 'Martin Gessner')
  FROM Account
 WHERE Type = 'Customer'

Polymorphic relationships are relationship fields where the referenced object can be one of multiple sObject types
- Ex: Who field of a Task can be a Contact or a Lead
- Ex: What field of an Event can be an Account or an Opportunity
- Two options for querying polymorphic fields in Apex: Type and TypeOf:

List<Task> tasks = [SELECT Id, Description
                      FROM Task
                     WHERE Who.Type IN ('Contact','Lead')];

List<Event> events = [SELECT TYPEOF What
                        WHEN Account THEN Website, NumberOfEmployees
                        WHEN Opportunity THEN StageName, Amount
                         END
                        FROM Event];

SELECT Id, Name, Mother__r.Name, Father__r.Name
  FROM Child__c
 WHERE Father__r.Name LIKE 'Bill%';

instanceOf method can be used to determine the object type of a polymorphic field
- Before the object can be used or passed to a method, it must be assigned to a variable of the appropriate type

for (Event e: events) {
    if (e.What instanceof Opportunity) {
        Opportunity opp = e.What;
        processOpportunity(opp);    // process opportunity
    } else if (e.What instanceof Account) {
        Account acc = e.What;
        processAccount(acc);        // process account
    }
}

subqueries can return empty sets if no child records are found
- Since a subquery also results in a list format, can use isEmpty() to determine if a subquery returned any results

for (Account a : accounts) {
    if (a.Contacts.isEmpty()) {
        System.debug(a.Name + ' has no contacts.');
    } else {
        System.debug(a.Name + ' has ' + a.Contacts.size() + ' contacts.');
    }
}

SOQL supports join patterns that utilize relationship traversal in path expressions:
- Outer Joins: first object and matching data from second object
- Inner Joins: inner joins can be used to return data from the first object that has related data in the second
- Anti Joins: first object that does not have related data in the second object

SOQL For Loop

SOQL Queries can be defined in a for loop statement, which is an efficient method for retrieving large data sets
- Iterate Results: can iterate over all sObject records returned by a SOQL query
- For Loop Syntax: for (DataType variable: [SOQL_Query]) {Code_Block}
- Single or Multiple: SOQL for loops can process records one at a time using an sObject variable, or in batches of 200 sObjects at a time using an sObject list
- Chunking Results: sObjects are retrieved using efficient chunking with calls to the query and queryMore methods of the SOAP API
- Avoiding Heap Size Limit: SOQL for loops should be used to process query results that return many records, in order to avoid the heap size limit

DML Statements

Data Manipulation Language, or DML, enables creating and modifying records in Salesforce
- Single or Multiple: operations that act on single or collection of records to insert, update, upsert, delete, or undelete
- If DML operations on records fail, an exception is thrown
- No Partial Success: standalone DML statements do not allow partial record processing when errors are encountered
Insert statement - follow insert keyword with the sObject or list of sObjects

Trigger OpportunityTrigger on Opportunity (before insert, after insert, before update, 
                                           after update, before delete, after delete) {
    if (Trigger.isAfter && Trigger.isInsert) {
        
        List<Contact> newConList = newList<Contact>();
        
        for (Opportunity opp : Trigger.New) {
            if (opp.AdditionalContactLastName__c != null) {
                Contact newCon = newContact();
                newCon.LastName = opp.AdditionalContactLastName__c;
                newCon.FirstName = opp.AdditionalContactFirstName__c;
                newCon.Email = opp.AdditionalContactEmail__c;
                newCon.AccountId = opp.AccountId;
                newConList.add(newCon);
            }
        }
        
        insert newConList;
    }
}

Update statement - follow update keyword with the sObject or list of sObjects
Upsert statement - used to either insert or update an existing record in one call
- Uses the record ID, a custom external ID field, or a standard field with idLookup set to true
  - If the key is not matched, a new object record is created
  - If the key is matched once, the existing object record is updated
  - If the key is matched multiple times, an error is generated
- Example below shows a collection that consists of existing records to update and enw records to create:

public class UsertDemoClass {
    public void UpsertDemoMethod(){
        List<Opportunity> oppToUpdateAndInsert = new List<Opportunity>();
        // Update Existing Opportunities and add to oppToUpdateAndInsert Collection
        for(Opportunity opp : [SELECT Id, Name FROM Opportunity LIMIT 10]) {
            opp.Comments__c = 'Timestamp:' + System.now();
            oppToUpdateAndInsert.add(opp);
        }
        // Create New Opportunities and add to oppToUpdateAndInsert Collection
        for(Integer i = 0; i < 4; i++){
            Opportunity opp = new Opportunity();
            opp.name = 'SampleOpp' + i;
            opp.StageName = 'Prospecting';
            opp.CloseDate = System.now().date() + 10;
            oppToUpdateAndInsert.add(opp);
        }
        //Update and Insert all Opportunities in one DML statement
        upsert oppToUpdateAndInsert;
    }
}

// Use execute anonymous block to demo the upsert operation in the class above
UpsertDemoClass up = new UpsertDemoClass();
up.UpsertDemoMethod()

Delete statement - follow delete with the sObject or list of sObjects
Undelete statement - follow undelete with the sObject or list of sObjects
- ALL ROWS keyword queries all rows for both top level and aggregate relationships including deleted records and archived activities
Merge statement - used for merging up to 3 records into a single record
- Use merge followed by the master record and any other records

public class DMLClass {
    public void dmlOperations(){
        //Insert statement
        Account acc = new Account (Name = 'Test Account');
        insert acc;

        //Update statement
        acc.BillingCity = 'Rome';
        update acc;

        //Upsert statement
        Contact con = new Contact(LastName = 'Tester');
        con.Title = 'CEO';
        upsert con;

        //Delete statement
        List<Account> badAccounts = [SELECT Id, Name 
                                       FROM Account 
                                      WHERE Name = 'ABC'];
        delete badAccounts;

        //Undelete statements
        List<Account> goodAccounts = [SELECT Id, Name 
                                        FROM Account 
                                       WHERE Name = 'Salesforce' 
                                         ALL ROWS];
        undelete goodAccounts;

        //Merge statement
        Account masterAccount = [SELECT Id, Name
                                   FROM Account
                                  WHERE Name = 'Salesforce'
                                  LIMIT 1];
        Account mergeAccount = [SELECT Id, Name
                                  FROM Account
                                 WHERE Name = 'Salesforce.com'
                                 LIMIT 1];
        merge masterAccount mergeAccount;
    }
}

Database Class Methods

The Database class provides an alternative way for making record changes in the database, as well as other methods
- Create and Manipulate data using Database class methods
- Allows partial success: Database class methods allow for partial processing, so if one record fails, processing continues
ConvertLead: Converts a lead into a business account, contact and opportunity by passing in lead records

Lead myLead = new Lead(FirstName='Willy', LastName='Wonka', Company='Chocolate Factory');
insert myLead;

Database.LeadConvert myConvert = new Database.LeadConvert();
myConvert.setLeadId(myLead.Id); // specify record Id of lead to convert

LeadStatus convertStatus = [
    SELECT Id, MasterLabel
      FROM LeadStatus
     WHERE IsConverted=true
     LIMIT 1
];
myConvert.setConvertedStatus(ConvertStatus.MasterLabel); // set status to converted

Database.LeadConvertResult result = Database.convertLead(myConvert);
System.assert(result.isSuccess());

EmptyRecycleBin: permanently deletes records from the Recycle Bin by specifying a list of sObjects or record Ids or an sObject
Rollback: restores the database to the state specified by a savepoint variable
SetSavePoint: creates a savepoint variable for restoring the database to that point using rollback
LeadConvert methods for Person Accounts - only available when Apex Lead Convert, Person Accounts, and Contacts to Multiple Accounts are enabled in an org
- getRelatedPersonAccount(): returns the record Id of the existing person account that the lead will be converted into
- setRelatedPersonAccountId(Id relatedPersonAccountId): sets the record id of the existing person account that the lead will be converted into - only required when updating the existing person account
- getRelatedPersonAccountRecord(): returns the entity record of the new person account that the lead will be converted into
- setRelatedPersonAccountRecord(Entity relatedPersonAccountRecord): sets the record entity of the new person account that the lead will be converted into
When partial save is required, the following Database class methods can be used
- allOrNone parameter defaults to true, which disables partial save. Enable by setting to false
- Database.insert(recordOrRecordsToInsert, allOrNone)
- Database.update(recordOrRecordsToUpdate, allOrNone)
- Database.upsert(recordOrRecordsToUpsert, fieldUsedForMatchingRecords, allOrNone)
- Database.merge(masterRecord, duplicateRecordsOrRecordIds, allOrNone)
- Database.delete(recordsOrRecordIdsToDelete, allOrNone)
- Database.undelete(recordOrRecordIdsToUndelete, allOrNone)

Account[] records = new List<Account>{
    new Account(Name='ACME');
    new Account()
};

// insert Database method is used here to insert records
Database.SaveResult[] results = Database.insert(records, false);

// Result of each record is stored in a SaveResult object
for (Database.SaveResult result : results) {
    if (result.isSuccess()) {
        System.debug('Id of newly created record: ' + result.getId());
    } else {
        // Retrieve the errors that were encountered
        for (Database.Error err : result.getErrors()) {
            System.debug(err.getStatusCode() + ': ' + err.getMessage());
            System.debug('Field errors:' + err.getfields());
        }
    }
}

Scenarios and Solutions

Reference FoF Slides