Lecture Notes: SOQL and SOSL in Salesforce

Introduction

• Presenter: Matt Gary
• Focus: Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL)
• Goals: To efficiently and effectively query data in Apex code and understand the differences and uses of SOQL and SOSL

Overview of SOQL and SOSL

• SOQL (Salesforce Object Query Language): Used to query records from Salesforce objects in Apex code
• SOSL (Salesforce Object Search Language): Used to search text across multiple objects
• Importance: Retrieving records for data manipulation in Salesforce
• Context: Methods to ensure efficient querying, especially with large datasets and growing tables
• Advanced topics: Asynchronous queries, indexing, and performance considerations

Setting Up and Using SOQL

Basics of SOQL Queries

• Syntax: SELECT [FIELDS] FROM [OBJECT] WHERE [CONDITION] [ORDER BY] [LIMIT]
• Example: SELECT ID, Name FROM Account WHERE Name = 'Taco'
• Use of developer console's query editor: Access through gear icon > Developer Console > Query Editor

Field and Object Names

• API Names: Used in queries and can differ from display names
• Custom Fields: Identified by __c suffix
• Retrieving API Names: Setup > Object Manager > Fields & Relationships__

Filtering Data

• WHERE Clause: Filter records based on conditions
  • Logical Operators: AND, OR, NOT
  • Comparison Operators: =, !=, <, <=, >, >=, LIKE, IN,
  • Example: SELECT ID, Name FROM Account WHERE Name = 'Taco' AND AnnualRevenue > 12000

Ordering and Limiting Data

• ORDER BY Clause: Order results
  • Syntax: ORDER BY [FIELD] [ASC|DESC] [NULLS FIRST|LAST]
• LIMIT Clause: Limit the number of records returned
  • Syntax: LIMIT [NUM]
• OFFSET Clause: Skip a set number of records
  • Syntax: OFFSET [NUM]

Querying Related Records

• Parent to Child (SELECT inside FROM): SELECT ID, Name, (SELECT FirstName FROM Contacts) FROM Account
• Child to Parent (Audit fields): Use dot notation, e.g., Account.Name

Advanced SOQL Queries

Aggregate Functions and Grouping

• Functions: AVG(), COUNT(), MIN(), MAX(), SUM()
• GROUP BY Clause: Group results by field
  • Example: SELECT MAX(AnnualRevenue), Type FROM Account GROUP BY Type
• HAVING Clause: Filter grouped results
  • Example: SELECT MAX(AnnualRevenue) FROM Account GROUP BY Type HAVING MAX(AnnualRevenue) > 950000000

Dynamic SQL in Apex

• Construct and run SOQL queries dynamically
• Use Database.query for dynamic strings

Debugging and Performance

• Limits Class: Use for benchmarking queries
  • Example: System.debug(Limits.getCpuTime())
• Query Plan Tool: Enable and use to ensure efficiency and avoid selectivity errors (setup in preferences)

SOSL (Salesforce Object Search Language)

Overview and Use Cases

• Focused on searching text across multiple objects
• Syntax differs from SOQL but is similar in some aspects
• Mainly used for creating custom search functionalities
• Limit: Can return a maximum of 2000 records

Basics of SOSL Queries

• Syntax: FIND {SearchTerm} IN [Scope] RETURNING [Objects] ([Fields])
• Example: FIND {Taco} IN NAME FIELDS RETURNING Account(Name, AnnualRevenue WHERE Name = 'Taco')
• Special Characters: Wildcards like * and ?*

Using SOSL in Apex

• Declaration: List<List<SObject>> results = [FIND 'Taco' IN ALL FIELDS RETURNING Account(Name), Contact(FirstName)]
• Iteration through results: Nested loops to deal with multiple object types

Advanced SOSL

• Dynamic SOSL: Create search strings dynamically and use Search.query method
• Functionality: Binding variables for safe and flexible queries (prevent injection vulnerabilities)

Security Considerations

• SOQL and SOSL Injection: Protect against malicious user input by escaping (use String.escapeSingleQuotes)
• With Security Enforced and Using Scope: Respect user permissions and scope

Conclusion

• Mastery of SOQL and SOSL is crucial for efficient Salesforce data manipulation
• Practical use cases in creating filters, managing performance, and securing queries
• Application in UI components, data retrieval, and bulk processing scenarios