In our previous article https://infotechabout.com/understand-soql/, we learned about SOQL and how to write SOQL in the developer console. Practically, we cannot keep writing these SOQL queries in the developer console forever. We need these SOQL queries in our Apex Logic to retrieve those records from the standard or custom salesforce and then if needed modify those records as well. If you are thinking to write SOQL in Apex is going to be complex coding, Not all, it’s very simple as we already know about Apex and, variables and SOQL queries well. So, we are just going to mix the syntax of both and we are going to create one single statement.
We are already familiar with the following SOQL query, where we retrieve the name and phone from the account object.
Simple SOQL Query:
SELECT Name, Phone FROM Account
Now we are going to assign the result of this SOQL query to an Apex variable, in this context we can use List, Set, and Map Statement to store the result/records of the SOQL query. There are some differences in the use of List, Set and Map statements in Apex for SOQL queries.
List – A list refers to a sorted collection of elements identified by their indices. List elements may be of any type of data–primitive collection, types or sObjects.
Set – Sets are an ordered collection of elements that do not contain duplicates. Set elements could be any type of data, including primitive collection, types or sObjects.
Map – A map is a collection of key-value pairs which map each unique key to one value. Keys and values may be any type of data, including primitive types collections, collections, sObjects, and collections.
Syntax:
- List<String1> String2 = new List<String1>([SOQL Select Query]);
- Set<String1> String2 = new Set<String1>([SOQL Select Query]);
- Map<Id, String1> String2= new Map<Id, String1>([SOQL Select Query]);
String1 = It represents the name of the Object.
String2 = It is a variable of the object where the result is to be assigned.
Id = It is an integer referring to a key value of pair.
You need to put the SOQL query within square brackets, and then only it is going to work. So, these square brackets tell your Apex compiler that you are going to write a SOQL query within these and your compiler is simply going to run this SOQL query, retrieve the results from it, and then going to put that result into the accounts variable.
Examples:
List Statement in Apex with SOQL:
List<Account> Accstring = new List<Account>([SELECT Name, Phone FROM Account]);
Set Statement in Apex with SOQL:
Set<Account> Accstring = new Set<Account>([SELECT Name, Phone FROM Account]);
Map Statement in Apex with SOQL:
Map<Id, Account> Accstring = new Map<Id, Account>([SELECT Name, Phone FROM Account]);
Now, if you want to get the name and phone from this account variable, you simply need to use the dot operator. This dot operator is being used everywhere. We use it in Apex. We use it in SOQL as well
Retrieve Fields Values
Use the dot(.) operator to retrieve fields values
If you want to retrieve a field value from your SOQL query result, then you will use it like Accstring.name, where account would be our variable and name is something that we are going to retrieve from the SOQL query.
You will understand it better when we will write the code in our developer console. So, let’s go back to the developer console and try out a few examples. So, let’s first write a simple SOQL query on the account object, and this is going to return all the account records in our Salesforce Org.
Now we will use this SOQL query in Apex as well. So, let’s open our anonymous window. Let’s write code that we need a list of accounts and we are going to name it as accounts, and here, let’s write our previous SOQL statement here.
List<Account> Accstring = new List<Account>([SELECT Name, Phone FROM Account]);
Remember, we need to write this SOQL query within square brackets, and let’s add a semicolon at the end.
If you will execute this code, we shouldn’t see any error but it’s going to open logs and if you will scroll down, then you will see that as part of this Apex execution, a SOQL query was executed and the SOQL query was, SELECT Name, Phone FROM account, and it returned 13 rows.
Now, let’s print these 13 rows in our own debug statements because if you click on debug only, nothing will show up here as we don’t have any debug statements in our code.
If I want to print the name and phone from each account then I need to use FOR loop for the List, Set, and Map, this is how we use For Loop:
Syntax:
For (Object String Variable: ListofString)
Object String is for an Object name, Variable assigned to object, and then List Name.
Examples:
List<Account> Accstring = new List<Account>([SELECT Name, Phone FROM Account]);
For (Account ac: Accstring) {
System.debug(‘Account Name: ‘+ac.Name+’ Account Phone: ‘+ac.Phone);
}
Local Variable is defined as the data type of the object and its’s holds the values of name and phone for each iteration next I’m going to put a debug statement here, and in this debug statement, I’ll simply say account name, and then I’m going to append my account name here, which will be account, that is my local variable. And as I explained, you need to use a dot operator to get the field value, which is name. So, account.name, then let’s add the phone as well, account phone, and then I’m going to use account. phone. So this way, we should be able to print all 13 accounts name and phone, because we are putting this debug statement within a For Loop. Let’s write this code and execute.
We do not see any error, which means our code is fine, let’s click on this debug-only checkbox, here, we are getting all our account names and phone. So, this is how you can use SOQL queries in Apex and here we have retrieved a list of accounts.
In place of the list of accounts, we can Set or Map statements to retrieve account records, which is also supported, and let’s see an example of that. Now, I’m going to fetch the records through Set Statement.
Examples:
Set<Account> Accstring = new Set<Account>([SELECT Name,Phone FROM Account]);
for (Account ac : Accstring)
{
System.debug(‘Account Name: ‘+ac.Name+’ Account Phone: ‘+ac.Phone);
}
Now let’s have an example of a Map Statement, a map of ID and account. The map is going to hold the account ID as the key and the account object’s value as the value, let’s name it as accountsMap and we are going to use our same SOQL query, a map very similar to how we are iterating over this account list, using a For Loop. So, let’s do that. Let’s write again the same code as Map Statement Syntax in the developer console.
Examples:
Map<Id,Account> Accstring = new Map<Id,Account>([SELECT Name,Phone FROM Account]);
for (Account ac : Accstring)
{
System.debug(‘Account Name: ‘+ac.Name+’ Account Phone: ‘+ac.Phone);
}
Oops, it will give an error
We are going the fetch value of map through our account map and you must remember that we can get all the values from a map using a method called values(). So, this values method is going to return a list of accounts, which is pretty similar to what we are seeing on line number one. And here we again have a local variable called to account, and the rest of the code remains the same. So, do you think it’s going to work?
Let’s rewrite again and execute.
Map<Id,Acccount) Accstring = new Map(Id,Account>([SELECT Name,Phone FROM Account]);
for (Account ac : Accstring.values())
{
System.debug(‘Account Name: ‘+ac.Name+’ Account Phone: ‘+ac.Phone);
}
Now it’s working fine and showing results of all Accounts lists in debug mode.
This is how you can get a map of I.D. and the account from an account field. However, remember for this business scenario the List is ideal and we always use a List statement to make a list/array for any standard and custom object records. In later articles, we see some advanced learning on salesforce Apex and SOQL and then we will see the uses of Set and Map as well.
Now, let’s see one more example with a list, where we will retrieve records from a custom object.
I’m going to rewrite the code in the developer console and this time instead of account, let’s get the data from my Patient Information Object, it’s my COVID-19 App object. Here we need to use the API name as Patient_Information__c, and c is for a custom object. So, Now I’m going to replace that in my query, and the same thing here as well, because remember, the list can accept an object type which is Patient Information here and we need to provide the API name of that object. Now we can get the name of the patient, the second field can be Age (Age_c) and the last third is Next Dose Date (Next_Dost_date_c) So, let’s copy it and paste it here. We need to make a few changes like variable name and data types as per custom object name and type.
// Retrieve Record from Custom Object Patient Information and assign to a List collection
List<Patient_information__c> patients = [SELECT Patient_Name__c, age__c, Next_Dose_Date__c FROM Patient_Information__c];
for(Patient_information__c patient : patients){
System.debug(‘Patient Name: ‘+patient.Patient_Name__c+’ Age: ‘+patient.age__c+’ Next Dost Date:
‘+patient.Next_Dose_Date__c);
}
Let’s execute this code and see debug log:
Moreover, you can use Mapid with a custom object like that now I am going to use the same coding of List with Map statement. For instance:
// Retrieve Record from Custom Object Patient Information and assign to a Map collection
Map<Id, Patient_information__c> patientsMap = new Map<Id, Patient_information__c>([SELECT Patient_Name__c, age__c, Next_Dose_Date__c FROM Patient_Information__c]);
for(Patient_information__c patient : patientsMap.values()){
System.debug(‘Patient Name: ‘+patient.Patient_Name__c+’ Age: ‘+patient.age__c+’ Next Dost Date: ‘+patient.Next_Dose_Date__c);
}
Let’s execute this code also and it would also work and show the same result in debug mode. But again I am explaining it’s better to use List instead of Map here in this case or example.
If you will scroll down, then we are getting all patient names along with the age, and Next dose date so this is how we use SOQL in APEX. Now I am going to explain how we will see how you can retrieve the data when you have a relationship in your SOQL.
SOQL Relationship Queries in Apex:
Now, we are going to learn how we can use a SOQL relationship query in Apex, if you remember or read my article https://infotechabout.com/soql-relationship-query/ regarding SOQL Relationships How do we write relationships query in SOQL and in which we used two types of objects one is parent and second is a child.
Let’s open the anonymous window again and copy the following SOQL relationship query that we have written on the developer console query editor.
SELECT Account.Name, Account.Rating, Name, Department, Title, (Select CaseNumber, Subject FROM Cases) FROM Contact ORDER BY Name
This query involves three objects, the first one is the contact, then the contact’s parent, which is the account, and then the contact’s child, which is the case. and when you are writing these SOQL queries in Apex, you always need to focus on the main object, which is contact here. So, if you’ll retrieve the result of this query, then you will retrieve it in a list of content. So, you will write a list of contact. You cannot write a list of accounts or a list of cases here, and let’s use a variable called contacts and then use square brackets.
List<Contact> contacts = [SELECT Account.Name, Account.Rating, Name, Department, Title, (Select CaseNumber, Subject FROM Cases) FROM Contact ORDER BY Name];
Here I’m going to highlight that you cannot use the list of cases or a list of accounts as our main object is contact So, you always need to use your main object as a datatype in your list collection, else it’s not going to work and from this main object, we can pass over to the parent objects as well and to our child objects as well.
So, let’s use a For Loop to actually print the contact value, and then we will see how we can print the account information and then cases information as well. Let’s use contact as our datatype, then a local variable con for contacts.
And here, I’m going to use a debug statement to print the contact’s detail, for instance, contact name, department, and title. Define local variables for Contacts like a con for contact name then dot and field name like we already use in our debug statement. For example:
// use list of ‘Contact’ to store the query result
// you cannot use list of ‘Case’, or list of ‘Account’ as
// our main object is ‘Contact’
List<Contact> contacts = [SELECT Account.Name, Account.Rating, Name, Department, Title, (Select CaseNumber, Subject FROM Cases) FROM Contact ORDER BY Name];
// use list of ‘Contact’ to store the query result
// you cannot use list of ‘Case’, or list of ‘Account’ as
// our main object is ‘Contact’
List<Contact> contacts = [SELECT Account.Name, Account.Rating, Name, Department, Title, (Select CaseNumber, Subject FROM Cases) FROM Contact ORDER BY Name];
for(Contact con : contacts){
System.debug(‘Contact Name: ‘+con.Name+’, Contact Department: ‘+con.Department+’, Contact Title: ‘+con.Title);
}
Let’s execute this code and we should see all our contacts in our debug statements.
So, here we are getting our contact name, contact department, and contact title, and we are getting all the contact records. So far, we are only printing the contact information in our debug logs.
Now we need to print our account and case information as well.
// use list of ‘Contact’ to store the query result
// you cannot use list of ‘Case’, or list of ‘Account’ as
// our main object is ‘Contact’
List<Contact> contacts = [SELECT Account.Name, Account.Rating, Name, Department, Title, (Select CaseNumber, Subject FROM Cases) FROM Contact ORDER BY Name];
for(Contact con : contacts){
System.debug(‘Contact Name: ‘+con.Name+’, Contact Department: ‘+con.Department+’, Contact Title: ‘+con.Title+’, Account Name: ‘+con.Account.Name+’, Account Rating: ‘+con.Account.Rating);
}
Now we are getting our account name and account rating as well. For some of the records, the account rating is null, because the rating is not populated on those records. But if the rating is there, then we are getting the rating in the results. Now, let’s focus on our last object, which is the case object and a case is a child object of contact here. So, there can be multiple cases for one single contact, and for that, we need another for loop to actually iterate over these cases. We didn’t need a for loop for the account object because there can be only one single parent, but the child records can be multiple. So, what I’ll do, is first we need to reach out to these cases. So again, we will use our relationship name, and then we can print these values. I’ll show you that now. So, here in this debug statement, we are printing the contact and account information. Now, after this, I’m going to use one more for loop, so that I can iterate over all the cases for that particular account. So this time, instead of writing contact, we will write a case. Then let’s use a local variable, maybe like case obj.
If you want to write case, it’s not supported, you need to give it another name because the case is a reserved word in Apex and then our list of cases. And how we can get that list of cases? We already have our contact variable, so we will use it, and then we will use our dot operator from here, using this dot operator, we will reach out to these cases. So, we will use this relationship name, which is cases and we will simply use it here to get the list of cases for a particular contact. And now we can use our debug statement to print the case information. So, let’s write one more debug statement, and I will say like, case number, and the case number should come from this case object, which is our local variable, dot the field name, which is this one, the case number, and let’s paste it here. Then let’s add the case subject as well, and this should come from the case obj dot subject. And let’s add our semicolon, and I think we are good. First, we are printing the contact and account information, then we are retrieving all the related cases for that contact, which can be multiple, and then we are printing those case information as well.
// use list of ‘Contact’ to store the query result
// you cannot use list of ‘Case’, or list of ‘Account’ as
// our main object is ‘Contact’
List<Contact> contacts = [SELECT Account.Name, Account.Rating, Name, Department, Title, (Select CaseNumber, Subject FROM Cases) FROM Contact ORDER BY Name];
for(Contact con : contacts){
System.debug(‘Contact Name: ‘+con.Name+’, Contact Department: ‘+con.Department+’, Contact Title: ‘+con.Title+’, Account Name: ‘+con.Account.Name+’, Account Rating: ‘+con.Account.Rating);
// iterate over the child records (Cases)
for(Case caseObj : con.Cases){
System.debug(‘Case Number: ‘+caseObj.CaseNumber+’, Case Subject:’+caseObj.Subject);
}
}
So, let’s execute this and wait for the code to execute and click on this debug-only checkbox.
So, if you see, first we are retrieving a contact name, for the first one, there’s no case, so we are directly moving to the second contact, then the third contact, and for this third contact, we have a case. So, that’s why we are getting the case and case subject. Then we are moving to the fourth contact. Then there’s one more case and then we are moving to the next contact. And this contact has multiple cases and we can confirm it from our query as well. Actually, let’s remove these Where conditions from our SOQL query, and let’s execute here. So that we can compare our debug logs and the actual SOQL query result, the first 2 are not having any cases. So, that’s why we are directly moving to the third one after printing the contact and account information. The third one has one single case that we can clearly see. It only has one single case, then maybe like the fourth one also has a case. And the fifth one has two cases, let’s see our fifth record has how many cases? this is the fifth one. It has one and two, yeah, it has two cases. So, that’s how we are iterating over our child objects as well. So, use the same strategy, you can directly print your parent account information using the dot operator. But if you want to reach out to the child object records, then you need one more for loop to actually iterate over those records and print them down.
How to use Bind Variables in Salesforce SOQL:
You can use Apex variable in SOQL using colon( : ). These variables are called bind variables.
This is very helpful in programming when you are using SOQL in Apex, generally, you store your data in a list or set or maybe in some other datatype variable, and using these variables directly in the SOQL query makes your query short and easy to read. For example, I could have a set of account IDs where, let’s say there are like 100+ account ids that are stored in this set, and now I can directly use this set in my SOQL query using the colon. So, this makes our SOQL query very short, as if you do not use a bind variable, then you need to construct this SOQL query for those hundred-plus accounts. And the other example is maybe like, you can store an account name or lead name in a string variable and then you can use that as well.
For Example:
Set<ID> accountIds = new Set<Id>();
[SELECT Id, Name, Rating FROM Account Where id in :accoutIds];|
String leadname = ‘Dataminds Technologies’;
[SELECT Id, Name FROM Lead WHERE Name = :leadName];
There are some constraints regarding using binding variables in SOQL:
- Bind Expressions can be used as:
- The filter literals in WHERE clause
- The value of the IN or NOT IN operator in WHERE clauses, allows filtering on a dynamic set of values. Note that this is of particular use with a list of IDs or Strings, though it works with lists of any type.
- The numeric value in LIMIT clause.
- The numeric value in OFFSET clause.
So now, let’s go back to our developer console and give it a try. So, here in my developer console, I’m going to create a list of strings where I’m going to store account names. And we are going to add a few account names to this list, so let’s copy these account names from our Salesforce Org.
We are going to use the ADD method from the list class, and this should be a string, let’s copy other accounts from salesforce org.
List<String> accountNames = new List<String>();
accountNames.add(‘Edge Communications’);
accountNames.add(‘Express Logistics and Transport’);
accountNames.add(‘Grand Hotels & Resorts Ltd’);
accountNames.add(‘Sample Account for Entitlements’);
List<Account> accounts = [SELECT Id, Name, Phone, Rating FROM Account WHERE Name IN :accountNames];
System.debug(‘Accounts: ‘+accounts);
System.debug(‘Accounts Size: ‘+accounts.size());
Dynamic SOQL Queries in APEX:
You can build your SOQL queries at runtime in apex code dynamically. Dynamic SOQL enables you to create more flexible applications.
For example, let’s say you have an account type variable in your Apex code, and if the value of this account type variable has the value ‘standard’, then you want to add a condition to your SOQL query where you want to match the rating as hot and the amount should be greater than 200,000. So, this is something that we will be building at runtime based on the account type variable value. To execute this query you cannot define it within the square braces, you need to use the database dot query method this method accepts the query string, and then it executes it.
When you write dynamic SOQL queries, you cannot define them in square braces, the way we used to do. The square braces SOQL Queries are more or less static, or they can only accept bind variables, but dynamic SOQL queries are far more flexible, and you need to use database. query method to execute these queries.
String dynQuery = ‘SELECT Id, Name from Account’;
if (accountType == ‘Standard’) {
DynQuery += ‘WHERE Rating=\’Hot\’ AND Amount > 200,000′;
}
List<Account> accounts = Database.query(dynQuery);
So, let’s go back to our console and let’s build a dynamic query in our anonymous window,
I’m going to create a string variable called account class and for now, I’m going to give it a value as class one and we are going to use this variable to build our dynamic SOQL query. For example, let’s say I have a query string, let me create a variable called queryString. You can name it whatever you want, and it’s querying some field from the account object. So, Id, name, phone, and rating FROM Account. So, this is a simple SOQL query and there’s nothing new that we have already done, but now we are going to add some more conditions to this SOQL query based on the account class variable value. So, I’m going to have an IF condition and I’m going to check if Account class equals class 1, then I will only fetch the accounts which are having a rating as hot and the amount as greater than 200k. So, I’m going to add some text to my query string variable. So, here I’ll add a Where condition, make sure that you give a space because we don’t have any space after account object, where the rating is hot. And here we need to escape our single quotes, and for that, we are going to use the forward slash, as the forward slash is used to escape a character and string, and then we are going to have a condition of amount, which should be greater than 100k, let’s say. So, this would be our SOQL query and this part of SOQL query will be built at runtime because this account class value could be anything. So, I’m going to have one more condition here. Else if account class equals class2, then I’m going to have a different condition here. So, this time the rating could be warm and the amount could be greater than 10K. Or else, I’ll simply retrieve all my accounts where the amount is greater than 10K.
String accountClass = ‘Class3’;
String queryString = ‘SELECT Id, Name, Phone, Rating FROM Account’;
if(accountClass == ‘Class1’){
queryString += ‘ WHERE Rating=\’Hot\’ AND Type=\’Prospect\”;
} else if(accountClass == ‘Class2’){
queryString += ‘ WHERE Rating=\’Warm\’ AND Type=\’Other\”;
} else {
queryString += ‘ WHERE Rating=\’Hot\”;
}
List<Account> accounts = Database.query(queryString);
System.debug(‘Accounts ‘+accounts);
System.debug(‘Accounts size ‘+accounts.size());
Here our query will be built at runtime because we are not sure what will be the value of the account class. In this example, we know that the account class value is class 1, but when you’ll be working on a project, you’ll have certain scenarios where the variable value could be anything, and based on the variable value, you need to construct your SOQL query at runtime. Now, let’s simply execute this query. It’s going to return us a list of accounts, let’s call it accounts. Now we are going to use the database dot query method here, which accepts the query string, which is this for our case. and now we can print the output.
This is a simple example of to use of Dynamic queries in SOQL and it is frequently used in our projects and database operation.
I hope you have enough understanding and good hands-on working on SOQL queries in Apex programming, if you have any questions feel free to comment and connect with me through email at yousuf@datamindstec.
