SOQL in Salesforce: A Comprehensive Guide
Introduction
Salesforce Object Query Language (SOQL) is a powerful tool that allows users to fetch records from Salesforce objects efficiently. SOQL provides a structured approach to querying data, including filtering, ordering, and aggregating records. This guide will cover SOQL syntax, best practices, query structures, and advanced concepts such as dynamic SOQL.
What is SOQL?
SOQL stands for Salesforce Object Query Language. It is used to retrieve one or more records from Salesforce objects based on user-defined conditions. SOQL queries can:
- Fetch data from a single object or multiple related objects.
- Apply filters to refine the query results.
- Utilize aggregate functions for summarizing data.
SOQL Query Syntax:
SELECT <FieldName/API Names> FROM <ObjectName>
[WHERE <Conditions>]
[GROUP BY <Column Names>]
[HAVING <Conditions>]
[ORDER BY <Column Names>]
[LIMIT <Number of Records To Return>]
[OFFSET <Number of Records to Skip>]
[FOR UPDATE]
[ALL ROWS]
Governor Limits
Salesforce imposes certain restrictions on SOQL to maintain system performance:
- Maximum SOQL Queries per Transaction: 100 queries.
- Exceeding this limit results in a System.LimitException: Too Many SOQL Queries : 101 error.
- Maximum Records Returned per Query: 50,000 records.
Best Practices for Writing SOQL Queries
- Avoid SOQL Queries Inside Loops: Running SOQL inside a loop can hit governor limits, causing errors.
- Select Only Required Fields: Fetching unnecessary fields increases query execution time.
Querying Data from Objects
Parent to Child Query
Retrieve an account’s related contacts:
SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account
For custom relationships, use __r instead of __c.
Example:
SELECT Id, Name, Contact_Number__c, Email_ID__c,
(SELECT Id, Name, Location_Name__c, Position_Status__c FROM Positions__r)
FROM Hiring_Manager__c
Child to Parent Query
Use dot notation to retrieve parent object data from the child object:
SELECT Id, FirstName, LastName, Account.Name FROM Contact
Dynamic SOQL
Dynamic SOQL allows query strings to be constructed at runtime in Apex code. This is useful for:
- Creating flexible applications based on user inputs.
- Handling dynamic filtering and field selection.
Syntax
String accountsQuery = ‘SELECT Id, Name, Industry FROM Account’;
List<Account> accountList = Database.query(accountsQuery);
Disadvantages of Dynamic SOQL
- Prone to SOQL Injection attacks.
- Use String.escapeSingleQuotes() to prevent injection vulnerabilities.
Example:
List<Contact> conList = Database.query(‘SELECT Id, Name FROM Contact WHERE FirstName = ” + String.escapeSingleQuotes(userInput) + ”’ );
Advanced SOQL Clauses
GROUP BY
Used to group records based on field values:
SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
HAVING
Filters grouped records:
SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry HAVING COUNT(Id) >= 3
FOR UPDATE
Locks records to prevent modification by other users:
List<Lead> leads = [SELECT Id, FirstName, LastName FROM Lead FOR UPDATE];
OFFSET
Skips a specified number of records (useful for pagination):
SELECT Id, Name FROM Account ORDER BY Name LIMIT 10 OFFSET 20
WHERE Clause
Filters records based on conditions:
SELECT Id, Name FROM Account WHERE Rating = ‘Hot’
LIKE Operator
Performs pattern matching with wildcards:
WHERE Name LIKE ‘R%’ — Names starting with ‘R’
WHERE Name LIKE ‘%Kumar’ — Names ending with ‘Kumar’
WHERE Name LIKE ‘_a%’ — Second character is ‘a’
Fetching Deleted Records
Retrieve deleted or archived records using ALL ROWS:
SELECT Id, IsDeleted FROM Account WHERE IsDeleted = true ALL ROWS
SELECT Id, IsDeleted FROM Account WHERE IsArchived = true ALL ROWS
Conclusion
SOQL is an essential tool for querying Salesforce data efficiently. By following best practices, leveraging advanced clauses, and utilizing dynamic SOQL wisely, developers can optimize performance and build scalable applications. Understanding SOQL ensures seamless interaction with Salesforce data and enhances application capabilities.