SOQL in APEX Programming - Information Technology Blogs - Salesforce - Content Writing - Digital Marketing (2023)

In our previous articlehttps://infotechabout.com/understand-soql/, we learned about SOQL and how to write SOQL in the developer console. We pretty much can't keep writing these SOQL queries in the developer console forever. We need these SOQL queries in our Apex logic to retrieve these standard or custom sales force records and if necessary modify these records as well. If you are thinking of writing SOQL in Apex, it will be complex coding, not all of it, it's very simple as we know well about Apex and SOQL variables and queries. So let's just mix up the syntax of both and create a single statement.

We are already familiar with the following SOQL query, where we retrieve the account object's name and phone number.

simple SOQL query:

SELECT Name, Phone FROM Account

Now let's assign the result of this SOQL query to an Apex variable, in this context we can useList,To place,and Map Statement to store the SOQL query result/records. There are some differences in using List, Array, and Map statements in Apex for SOQL queries.

List -A list refers to an ordered collection of elements identified by their indices. List elements can be of any data type: primitive collection, types, or sObjects.

To place -Sets are an ordered collection of elements that do not contain duplicates. Array elements can be any data type, including collection primitive, types, or sObjects.

Map -A map is a collection of key-value pairs that map each unique key to a value. Keys and values ​​can be any data type, including collections of primitive types, collections, sObjects, and collections.

Syntax:

1. List<String1> String2 = new List<String1>([SOQL select query]);
2. Set<String1> String2 = new Set<String1>([SOQL select query]);
3. Map<Id, String1> String2= new Map<Id, String1>([SOQL select query]);

Current1= Represents the name of the Object.
Cadena2= It is a variable of the object where the result must be assigned.
I WENT= is an integer that refers to a key pair value.

You need to enclose the SOQL query in square brackets and only then will it work. So those square brackets tell the Apex compiler that you're going to write a SOQL query inside them and your compiler will simply run that SOQL query, retrieve the results, and then put that result into the accounts variable.

Examples:

List declaration in Apex with SOQL:

List<Account> Accstring = new List<Account>([SELECT Name, PhoneFROM Account]);

Define the statement in Apex with SOQL:

Set<Account> Accstring = new Set<Account>([SELECT Name, PhoneFROM Account]);

Map declaration 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 number from this account variable, just use the dot operator. This dot operator is used everywhere. We use it in Apex. We also use it in SOQL

Retrieve field values

Use the dot (.) operator to retrieve field values

If you want to retrieve a field value from the result of your SOQL query, you would use it likeAccstring.name, where count would be our variable and name is something we're going to retrieve from the SOQL query.

You'll understand better when we write the code in our developer console. So let's go back to the developer console and try some examples. So let's first write a simple SOQL query against the account object and this will return all the account records in our Salesforce org.

We will now also use this SOQL query in Apex. So let's open our incognito window. Let's write the code that we need a list of accounts and let's name them 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 wrap this SOQL query in square brackets and add a semicolon at the end.

If you run this code, we won't see any errors, but it will open logs, and if you scroll down, you'll see that as part of this Apex execution, a SOQL query was executed, and the SOQL query was, SELECT Name, Phone FROM count and returned 13 rows.

Now, let's print those 13 lines into our own debug statements, because if you just click debug nothing will show up here as we don't have any debug statements in our code.

If I want to print each account name and phone I need to useIn loopFor theList,To place, youMap, this is how we use For Loop:

Syntax:
To(object string variable: ListofString)

The object string is for an object name, variable assigned to the object, then list name.

Examples:
List<Account> Accstring = new List<Account>([SELECT Name, Phone FROM Account]);
To(Account: Accstring) {
System.debug('Account Name: '+ac.Name+' Account Phone: '+ac.Phone);
}

The local variable is set to the object's data type and contains the name and phone values ​​for each iteration. Then I'll put a debug statement here, and in that debug statement I'll just say the account name, and I'll add my account name here, which will be account, that's my local variable. And as I explained, you have to use a dot operator to get the value of the field, which is the name. Then,account name, so let's add the phone too, the account phone, and then I'll useaccount. telephone. So we should be able to print the name and phone number of all 13 accounts because we are putting this debug statement inside aIn loop. Let's write this code and run it.

We don't see any errors, which means our code is fine, let's click this debug only checkbox, here we are getting all the names and phone numbers of our accounts. So this is how you can use SOQL queries in Apex and here we retrieve a list of accounts.

Instead of the account list, we can define or assign account statements to retrieve account records, which is also supported, and let's see an example of this. Now, I will search the logs throughTo placeDeclaration.

Examples:
Set<Account> Accstring = new Set<Account>([SELECT Name,Phone FROM Account]);
to (Account: Accstring)
{
System.debug('Account Name: '+ac.Name+' Account Phone: '+ac.Phone);
}

Now let's see an example of extracting a map, an id and an account map. The map will contain the account id as the key and the value of the account object as the value, let's call it accountMap and we'll use our same SOQL query, a map very similar to how we're iterating over this list of accounts, using aIn loop. So let's do this. Let's write the same code as Map Statement Syntax again in the developer console.

Examples:
Map<Id,Account> Accstring = new Map<Id,Account>([SELECT Name,Phone FROM Account]);
to (Account: Accstring)
{
System.debug('Account Name: '+ac.Name+' Account Phone: '+ac.Phone);
}

oops will give an error

Let's get the value of the map through the map of our account and remember that we can get all the values ​​of a map using a method calledvalues(). So this values ​​method will return a list of accounts, which is pretty similar to what we're seeing on line number one. And here again we have a local variable called account, and the rest of the code remains the same. So do you think it will work?

Let's rewrite again and run.

Map<Id,Account) Accstring = new Map(Id,Account>([SELECT Name,Phone FROM Account]);
to (Account ac: Accstring.values())
{
System.debug('Account Name: '+ac.Name+' Account Phone: '+ac.Phone);
}

Now it works fine and shows the results of all account lists in debug mode.

This is how you can get an I.D. and the account from an account field. Remember though that for this business scenario the list is ideal and we always use a list declaration to create a list/array for any standard and custom object records. In later articles, we'll cover some advanced learning about Salesforce Apex and SOQL, and then we'll look at Set and Map uses as well.

Now, let's see one more example with a list, where we will retrieve the records of a custom object.
I'm going to rewrite the code in the developer console, and this time, instead of the account, let's get the data from my patient information object, it's my COVID-19 application object. Here we need to use the API name like Patient_Information__c and c is for a custom object. So now I'm going to override 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 patient's name, the second field can be Age (Age_c) and the last third is Next Dose Date (Next_Dost_date_c) So let's copy and paste here. We need to make some changes like the variable name and data types based on the custom object name and type.

// Retrieves the patient information record from the custom object and assigns it to a list collection

List<Patient_Information__c> pacientes = [SELECT Patient_Name__c, Age__c, Date_of_Next_Dose__c FROM Patient_Information__c];

for(Patient_information__c patient: patients){

System.debug('Patient name: '+patient.Patient_Name__c+' Age: '+patient.age__c+' Date of next Dost:
‘+paciente.Next_Dose_Date__c);

}

Let's run this code and see the debug log:

Also you can use Mapid with a custom object like this now I'm going to use the same List coding with Map declaration. For example:

// Retrieves the patient information record from the custom object and assigns it to a map collection

Map<Id, Patient_Information__c>pacientesMap = new Map<Id, Patient_Information__c>([SELECT Patient_Name__c, age__c, Next_Dose_Date__c FROM Patient_Information__c]);

for(Patient_information__c paciente :patientMap.values()){

System.debug('Patient name: '+patient.Patient_Name__c+' Age: '+patient.age__c+' Next dose date: '+patient.Next_Dose_Date__c);

}

Let's run this code as well and it will also work and show the same output in debug mode. But again, I'm explaining that it's better to use List instead of Map here in this case or example.

If you scroll down we'll get all the patients' names along with their age and next dose date, this is how we use SOQL in APEX. Now I will 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 let's learn how we can use a SOQL relationship query in Apex if you remember or read my article https://infotechabout.com/soql-relationship-query/ on SOQL relationships How do we write a relationship query in SOQL? and in which we use two types of objects, one is parent and second is child.

Let's open the incognito window again and copy the following SOQL relation query that we wrote into the developer console's query editor.

SELECT Account.Name, Account.Classification, Name, Department, Title, (Select Case Number, Case Subject) FROM Contact ORDER BY Name

This query involves three objects, the first is the contact, then the parent of the contact, which is the account, and the child of the contact, which is the case. and when you write these SOQL queries in Apex, you should always focus on the main object, which is the contact here. So if you retrieve the result of that query, it will be retrieved in a content list. So, you are going to write a list of contacts. You can't write a list of accounts or a list of cases here, and we're going to use a variable called contacts and then use square brackets.

List<Contact> contacts = [SELECT Account.Name, Account.Classification, Name, Department, Title, (Select Case Number, Subject OF Cases) FROM Contact ORDER BY Name];

Here I will point out that you cannot use case list or an account list as our main object is contact. So you should always use its parent object as a datatype in your list collection, otherwise it won't work. and from this main object we can also go to the main objects and also to our child objects.

So let's use aIn loopto actually print the contact amount, and then we'll look at how we can print the account information as well as the case information. Let's use contact as our data type, so a local variablefraudfor contacts.

And here, I'm going to use a debug statement to print out the contact details, for example the contact's name, department, and job title. Define local variables for the contacts like a con for the contact name, then dot and field name like we already used in our debug statement. For example:

// use the 'Contact' list to store the query result
// cannot use 'Case' or 'Account' list as
// our main object is 'Contact'

List<Contact> contacts = [SELECT Account.Name, Account.Classification, Name, Department, Title, (Select Case Number, Subject OF Cases) FROM Contact ORDER BY Name];

// use the 'Contact' list to store the query result
// cannot use 'Case' or 'Account' list as
// our main object is 'Contact'

List<Contact> contacts = [SELECT Account.Name, Account.Classification, Name, Department, Title, (Select Case Number, Subject OF Cases) FROM Contact ORDER BY Name];

to (contact: contacts) {

System.debug('Contact Name: '+with.Name+', Contact Department: '+with.Department+', Contact Title: '+with.Title);

}

Let's run this code and we'll see all of our contacts in our debug statements.

So here we get our contact name, contact department and contact title and we get all contact records. So far, we've only printed contact information in our debug logs.

Now we also need to print out our account and case information.

// use the 'Contact' list to store the query result
// cannot use 'Case' or 'Account' list as
// our main object is 'Contact'

List<Contact> contacts = [SELECT Account.Name, Account.Classification, Name, Department, Title, (Select Case Number, Subject OF Cases) FROM Contact ORDER BY Name];

to (contact: contacts) {

System.debug('Contact Name: '+with.Name+', Contact Department: '+with.Department+', Contact Title: '+with.Title+', Account Name: '+with.Account.Name+' , Account Qualification: '+with.Account.Qualification);

}

Now we are also getting the name and rank of the account. For some of the records, the account sort is null because sort has not been completed on those records. But if the qualification is there, then we are getting qualification 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 a single contact and for that we need another for loop to iterate over those cases. We don't need a for loop for the account object because there can only be a single parent, but the child records can be multiple. So what I'm going to do is we're going to get to those cases first. Again, we'll use the name of our relationship and then we can print those values. I will show you this now. So here in this debug statement we are printing the contact and account information. Now, after that, I'm going to use one more for loop, so I can iterate over all the cases for that particular account. So this time, instead of writing a contact, let's write a case. So let's use a local variable, perhaps like case obj.

If you want the case type not supported, you should give it another name because case is a reserved word in Apex and then in our case list. And how can we get this list of cases? We already have our contact variable, so we're going to use that, and then we're going to use our dot operator from here, using that dot operator, we'll get to these cases. So we're going to use that relationship name, which is cases, and we're just going to use it here to get the list of cases for a specific contact. And now we can use our debug statement to print the case information. So let's write one more debug statement, and I'll say type, case number, and the case number should come from this case object, which is our local variable, put the field name, which is this, the case number and we're going to paste it here. So let's add the subject of the case as well, and that should come from the point subject of the case's obj. And we're going to add our semicolon, and I think we're done. First, we print the contact and account information, then we retrieve all the cases related to that contact, which could be multiple, and we also print the case information.

// use the 'Contact' list to store the query result
// cannot use 'Case' or 'Account' list as
// our main object is 'Contact'

List<Contact> contacts = [SELECT Account.Name, Account.Classification, Name, Department, Title, (Select Case Number, Subject OF Cases) FROM Contact ORDER BY Name];

to (contact: contacts) {

System.debug('Contact Name: '+with.Name+', Contact Department: '+with.Department+', Contact Title: '+with.Title+', Account Name: '+with.Account.Name+' , Account Qualification: '+with.Account.Qualification);

// iterates over the child records (Cases)

for(Case caseObj : con.Cases){

System.debug('Case number: '+caseObj.CaseNumber+', Case subject:'+caseObj.Subject);

}

}

So let's run that and wait for the code to run and click on this debug only checkbox.

So if you see, first we are retrieving the name of a contact, for the first one there is no case, then we go directly to the second contact, then to the third contact and for that third contact we have a case. So that's why we're getting the case and the subject of the case. So we're moving into the fourth contact. Then there is one more case and then we move on to the next contact. And this contact has several cases and we can also confirm it in our consultation. In fact, let's remove these where conditions from our SOQL query and run them here. So that we can compare our debug logs and the actual SOQL query output, the first 2 have no cases. That's why we're going directly to the third party after printing out the contact and account information. The third has a single case that we can clearly see. He only has one case, so maybe like the fourth he also has a case. And the fifth has two cases, let's see our fifth record, how many cases does it have? this is the fifth. It has one and two, yes it has two boxes. So that's how we're also iterating over our child objects. So, using the same strategy, you can directly print your main account information using the dot operator. But if you want to access the child object's records, you need one more for loop to iterate over those records and print them.

How to use binding variables in Salesforce SOQL:

You can use Apex variable in SOQL usingtwo points ( : ). These variables are called binding variables.

This is very useful in programming when using SOQL in Apex, you usually store your data in a list or array or maybe some other data type 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, say, there are 100+ account IDs stored in that set, and now I can use that set directly in my SOQL query using a colon. So this makes our SOQL query very short, for example if you don't use a binding variable you need to create this SOQL query for 100+ accounts. And the other example is maybe, you can store an account name or a lead name in a string variable and then you can use that as well.

There are some restrictions on using binding variables in SOQL:

1. Link expressions can be used like:

1. Filter literals in the WHERE clause
2. The value of the IN or NOT IN operator in WHERE clauses allows you to filter a dynamic set of values. Note that this is especially useful with a list of IDs or strings, although it works with lists of any type.
3. The numeric value in the LIMIT clause.
4. The numeric value in the OFFSET clause.

Now, let's go back to our developer console and give it a try. So here in the developer console I'm going to create a list of strings where I'm going to store the account names. And we're going to add some account names to this list, so we're going to copy these account names from our Salesforce org.

We are going to use the ADD method of the list class, and this should be a string, we are going to copy other accounts from salesforce org.

List<String> nomes de contas = new List<String>();
accountNames.add('Perimeter Communications');
accountNames.add('Logistics and Express Transport');
account names.add('Grand Hotels & Resorts Ltd');
accountNames.add('Example account for entitlements');
List<Account> accounts = [SELECT Id, Name, Phone, Qualification FROM Account WHERE Name IN :account names];
System.debug('Accounts: '+accounts);
System.debug('Size of accounts: '+accounts.size());

Dynamic SOQL queries in APEX:

You can build your runtime SOQL queries in apex code dynamically. Dynamic SOQL allows you to build more flexible applications.

For example, suppose you have an account type variable in your Apex code, and if the value of this account type variable has the value 'estándar', then you want to add a condition to your SOQL query where you want to match the qualification. so hot and the amount must be more than 200,000. This is something we'll construct at runtime based on the value of the account type variable. To run this query, you cannot enclose it in square brackets, you must use the database dot query method. This method accepts the query string and executes it.

When you write dynamic SOQL queries, you can't enclose them in braces like we used to. Supporting SOQL queries are more or less static or can only accept binding variables, but dynamic SOQL queries are much more flexible and need to use the database. query method to run these queries.

String dynQuery = 'SELECT Id, account name';
if (accountType == 'Default') {
DynQuery += ‘WHERE Rating=\’Hot\’ AND Amount > 200.000′;
}
List<Account> accounts = Database.query(dynQuery);

So let's go back to our console and create a dynamic query in our incognito 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're going to use that variable to create our dynamic SOQL query. For example, say I have a query string, let me create a variable called queryString. You can name it whatever you like, and it's looking up some field in the account object. So ID, name, phone and account rating. So this is a simple SOQL query and there's nothing new we've done yet, but now we're going to add some more conditions to this SOQL query based on the value of the account class variable. So I'm going to have an IF condition and I'm going to check if the account class is the same as class 1, so I'm just going to look for the accounts that are rated as high and are worth more than 200k. So I'm going to add some text to my query string variable. So here I'm going to add a Where condition, make sure you leave a space because we don't have any spaces after the account object where the sort is good. And here we need to escape our single quotes, and for that we are going to use the slash, as the slash is used to escape a character and a string, and then we will have a quantity condition, which must be greater than 100k, say. So this would be our SOQL query and this part of the SOQL query will be created at runtime because the value of the account class can be anything. So I'm going to have one more condition here. Otherwise, if the account class is the same as class 2, then I have a different condition here. So this time the rating can be hot and the value can be more than 10k. Or I will just recover all my accounts where the amount is more than 10K.

String classAccount = 'Class3';
String queryString = 'SELECT Id, Name, Phone, Account Classification';
if(clasecuenta == 'Classe1'){
queryString += ‘ WHERE Clasificación=\'Hot\' AND Tipo=\'Prospecto\";
} else if(clasecuenta == 'Clase2'){
queryString += ‘ WHERE Rating=\'Warm\' AND Type=\'Other\";
} the rest {
queryString += ‘ WHERE Rating=\'Hot\";
}
List<Account> accounts = Database.query(queryString);
System.debug('Accounts'+accounts);
System.debug('Size of accounts'+accounts.size());

Here our query will be built at runtime because we are not sure what the value of the account class will be. In this example we know that the account class value is class 1 but when working on a project you will have certain scenarios where the variable value can be anything and depending on the variable value you should create your query SOQL at runtime. Now let's just run this query. It will return a list of accounts, let's call them accounts. Now we're going to use the database point query method here, which accepts the query string, which is it for our case. and now we can print the output.
This is a simple example of using dynamic queries in SOQL and is used frequently in our database projects and operations.

Hope you have enough understanding and good practical work on SOQL queries in Apex programming. If you have any questions, feel free to comment and contact me at yousuf@datamindstec.