SOQL Queries in Apex
Salesforce developers rely heavily on SOQL queries in Apex to interact with database. Whether you are a complete newbie or seasoned coder, mastering SOQL is crucial for building efficient and scalable applications.
Syntax of SOQL query in Apex
nameOfSObject variableName = [SOQL_Query]; /*OR*/ List<nameOfSObject> variableName = [SOQL_QUERY]; /*OR*/ Map<Id, nameOfSObject> variableName = new Map<Id, nameOfSObject>([SOQL_QUERY]);
Though, salesforce does not restrict you to use a sObject variable to collect returned records from SOQL but, it is advisable to use collection variables or collection of sObject variables to store returned records from SOQL.
Retrieve field values from SOQL query in Apex
Use dot (.) operator to retrieve field values.
Syntax
variableName.fieldName
Example
List<Opportunity> oppList = [SELECT Id, Name, StageName
FROM Opportunity];
//Access the first element of oppList
System.debug('1st element '+oppList[0].Name+' '+oppList[0].StageName);
//for loop to iterate over all values present in oppList and print Name as well as Stage
for(Opportunity opp : oppList){
System.debug('Name: '+opp.Name+' '+'Stage: '+opp.StageName);
}
Binding Variables in SOQL
You can use Apex variables in SOQL using a colon (:). These variables are called bind variables. You can use binding variable after WHERE clause and in LIMIT and OFFSET clause.
String Stage = 'Closed Won';
//Query all opportunities with closed won stage.
List<Opportunity> oppList = [SELECT Id, Name, Amount
FROM Opportunity
WHERE StageName =: Stage];
System.debug('oppList size '+oppList.size());
for(Opportunity opp : oppList){
System.debug('Name '+opp.Name+' '+'Amount '+opp.Amount);
}
Handling Aggregate Queries in Apex
AggregateResult object is used to store the results of aggregate queries. AggregateResult is a read-only object. Please checkout SOQL functions to learn about aggregate queries.
The get() method uses alias, fieldname or expri when no alias is provided. i in expri represents order of aggregated fields without alias.
Syntax
List<AggregateResult> variableName = [Aggregate_SOQL_query];
variableName[0].get('fieldName/alias/expri');
Example
String Stage = 'Closed Won';
List<AggregateResult> agrList = [SELECT MIN(Amount) minAmount, MAX(Amount)
FROM Opportunity
WHERE StageName =: Stage];
System.debug('agrList size '+agrList.size());
for(AggregateResult agr : agrList){
System.debug(agr.get('minAmount')+' '+agr.get('expr0'));
}
SOQL For Loop
SOQL for loop can process one record at a time using single sObject, or in chunk of 200 using sObject list. It uses efficient chunking with call to query or querymore methods of SOAP API. It is used to limit heap size.
Syntax
for(sObjectName variableName : [SOQL_Query]){
//logic here
}
/*OR*/
for(List<sObjectName> variableName : [SOQL_Query]){
//logic here
}
sObject list format is optimal choice if you must use DML statement within for loop because, this format executes code within for loop once for 200 sObject records
Example of SOQL for loop
String Stage = 'Closed Won';
for(Opportunity opp : [SELECT Id, Name
FROM Opportunity
WHERE StageName =: Stage]){
System.debug('Opp>>> '+Opp);
}
System.debug('CPU time in milliseconds >>> '+Limits.getCpuTime());
System.debug('heap size in bytes>>> '+Limits.getHeapSize());
Output
Example of List Assignment for loop
String Stage = 'Closed Won';
List<Opportunity> oppList = [SELECT Id, Name
FROM Opportunity
WHERE StageName =: Stage];
for(Opportunity opp : oppList){
System.debug('Opp>>> '+Opp);
}
System.debug('CPU time in milliseconds >>> '+Limits.getCpuTime());
System.debug('heap size in bytes>>> '+Limits.getHeapSize());
Output
Between SOQL for loop and List Assignment for loop which one should we choose? after going through the output of both approaches, we can conclude that SOQL for loop is suitable when heap size is priority and List assignment for loop keep CPU cycles on priority.
Best practices for using SOQL in Apex
Use Collection Variables to store results of SOQL query
This approach will help you to avoid following exceptions List has no rows for assignment to sObject and List has more than one row for assignment to sObject exception. This exception occurs when you use single sObject variable to store result of SOQL query.
Avoid SOQL queries inside Loops
Writing SOQL inside loop will exhaust governor limit of 100 SOQL queries in a transcation.
Use Selective Filters
Use Indexed fields such as Id , Name , CreatedDate etc in WHERE clause of SOQL query. This will help us to improve performance and avoid unexpected runtime exceptions.
Query only required fields
It reduces the amount of data retrieved, which improves efficiency and avoid heap limit exceptions.
Use SOQL for Loops for large data volumes
It will process query results in batches under the hood, reducing memory usage and avoiding heap exceptions.
Whether you’re writing simple queries or complex logics, mastering SOQL queries in apex is essential for salesforce development. Follow best practices, handle exceptions gracefully, and optimize performance using SOQL for loops.
