SOQL in Apex

• admin
• December 31, 2022
• 1,152 Views
• 0 Comments

SOQL in Apex is Salesforce Object Query Language. What it actually means:

• Salesforce: Obviously, this is the platform where SOQL used.
• Objects: SOQL is only used to work with objects.
• Query: Request to the database to get some data.
• Language: SOQL is a separate programming language. Yes, you can really think of it as a completely different language.

Where can we write SOQL? For starters, we can use the Query Editor tab in the developer console.

SOQL in Apex Syntax

The most basic SOQL syntax:

SELECT Field1, Field2, Field3
FROM SObject

Example #1 – Query Contact

SELECT Id, FirstName, LastName, Email
FROM Contact

Result

The result will depend on what data you have in your org. If you don’t see anything, you probably don’t have any contacts. When we execute the query, we immediately see a table that contains the following rows: Id, FirstName, LastName, Email. This is exactly the order that we requested in our SOQL query. We can see our records as well. Every record has its own row. 

So, what happened?

1. The “Execute” button was clicked.
2. Salesforce recognized that there’s an SOQL query.
3. The SOQL query was executed on your Database (org).
4. A query response was formed by the SOQL engine.
5. Salesforce displayed the result of the SOQL query in the developer console.

It doesn’t have to be the developer console, though. It could be anything—your IDE, another system or Apex code.

Example #2 – Query a custom object

For this example, you can take any custom object that you have in your system. I have a custom object called Student__c. Therefore, I can execute the following query:

SELECT Name, Id, OwnerId, CreatedById, LastModifiedById
FROM Student__c

And the result will be similar. There’s no difference from a standard object!

SOQL Keywords

We’ve seen two keywords so far: SELECT and FROM. There are tons of others, but let’s focus on those that will cover 90% of your needs as a Salesforce developer.

WHERE keyword

The WHERE keyword is used to filter results in your query:

SELECT Field1, Field2, Field3
FROM SObject
WHERE CONDITION 

Conditions always consist of three elements:

1. Field name: The API name of the field from your object.
2. Comparison sign: It can be any of these values: =, !=, >, >=, <, <= (and some more).
3. Value.

SELECT Field1, Field2, Field3
FROM SObject
WHERE FieldName sign Value

Example #3 – Opportunity Amount

SELECT Name, Amount, StageName, CloseDate, AccountId
FROM Opportunity
WHERE Amount > 100000

Here:

• Amount: The API field name from the Opportunity object.
• >: The comparison sign.
• 100000: The value for comparison.

This query will give us all the opportunities where the Amount field is greater than 100,000. Here’s the result:

Example #4 – Query based on Id

This example will be more relevant for the combination of Apex and SOQL. We can query based on the Id, and as result, we’ll always get just one record because an Id in Salesforce is unique. You should use an Id that exists in your Salesforce org.

SELECT Id, Name, StageName, CloseDate
FROM Opportunity
WHERE Id = '0067Q00000DTtwJQAT'

Output for my org:

Combining conditions

You can only use three logic operators to combine conditions: AND, OR and NOT. But you can combine them endlessly!

you can combine them endlessly!
SELECT Field1, Field2, Field3
FROM SObject
WHERE (CONDITION1 AND CONDITION2) OR NOT(CONDITION2 AND CONDITION3)

Example #5 – Combining conditions

We can use the same logical expressions that we know from booleans and formulas to combine different conditions in SOQL. This query will find all the opportunities in the “Closed Won” stage with an amount greater than 100,000 and with an account on the opportunity.

SELECT Name, Amount, StageName, CloseDate, AccountId
FROM Opportunity
WHERE Amount > 100000 AND StageName = 'Closed Won' AND AccountId != NULL

• Amount > 100000: This condition makes sure that only opportunities with an amount greater than 100,000 are included in the query result.
• StageName = ‘Closed Won’: This condition makes sure that only opportunities in the “Closed Won” stage are included in the query result.
• AccountId != NULL: This condition makes sure that only opportunities where the AccountId field isn’t empty are included in the query result.
• AND: This logical operator ensures that the query result only includes opportunities where all three conditions are true.

Example #6 – High-value leads

The following query will return all leads that are either ranked as Hot or have a revenue greater than 100000. The last condition makes sure that none of the leads has a status equal to Closed – Not Converted.

SELECT Name, LeadSource, Rating, AnnualRevenue, City, Industry
FROM Lead
WHERE (AnnualRevenue >= 100000 OR Rating = 'Hot') AND (Status != 'Closed - Not Converted')

The result will depend on what data you have. Here’s what I got for my org:

LIMIT keyword

LIMIT limits the amount of records in the query result.

SELECT Field1, Field2, Field3
FROM SObject
LIMIT X

Here:

• X: Can be any integer number between 1 and 200.

LIMIT is mostly used in combination with Apex to query for just one record.

Example #7 – Limit opportunities

Display any five opportunities:

SELECT Name, Amount, StageName, CloseDate, AccountId
FROM Opportunity
LIMIT 5

Example #8 – Limit with where

We can combine both keywords to include only relevant opportunities and limit their number.

SELECT Name, Amount, StageName, CloseDate, AccountId
FROM Opportunity
WHERE Amount > 100000 AND StageName = 'Closed Won' AND AccountId != NULL
LIMIT 3

Here:

• SELECT: Specifies the fields to display in the query result.
• FROM: Specifies the object to query from.
• WHERE: Specifies which opportunities to include.
• LIMIT: Specifies how many opportunities to include.

The result for my org:

More keywords

Consult the table from the Salesforce Ben Guide to read about more SOQL keywords. The most important are WHERE, LIMIT and IN.

SOQL with Apex

If we run SOQL in the developer console, we get the result as a table. In Apex, the result of an SOQL query is usually one of these data types:

• List
• Single record

It can be a whole lot more, but these two will cover 90% of your needs as a junior developer. Let’s go over a few examples for each of these cases.

SOQL returns a List

The most common way of using SOQL in Apex is saving the result of the query into a List. Unlike with the developer console, we need to enclose SOQL query code into []. The syntax is as follows:

List<Sobject> sobjects = [SOQL];

Example #9 – Opportunities

List<Opportunity> opportunities = [
    SELECT Name, Amount, StageName 
    FROM Opportunity 
    LIMIT 3
];

To compare this approach with using the developer console, we can imagine every table row as a list element. Actually, the query result is always the same. It’s just a matter of where it’s displayed.

Example #10 – Opportunities and operations

With lists, we can perform the operations that we know from our List article. What do you think will be printed out in this case?

List<Opportunity> opportunities = [
    SELECT Name, Amount, StageName, CloseDate, AccountId
    FROM Opportunity
    WHERE Amount > 100000 AND StageName = 'Closed Won' AND AccountId != NULL
];

System.debug(opportunities.size());

The answer: It depends on how many qualified opportunities you have in your org. For me, the output is:

9

Example #11 – Display each opportunity

This example shows the most common scenario of SOQL application. First, we get some data into a list, then we loop over this data.

List<Opportunity> opportunities = [
    SELECT Name, Amount, StageName, CloseDate, AccountId
    FROM Opportunity
    WHERE Amount > 100000 AND StageName = 'Closed Won' AND AccountId != NULL
];

for(Opportunity opp : opportunities) {
    System.debug(opp);
}

Output

SOQL returns a single record

We can return a single record from an SOQL query in Apex by including the LIMIT 1 keyword in the query. It’s mostly used when we query based on the Id of a record.

Example #12 – Single account

In this example, we can assign the query result to a single record—not the whole List, as in previous examples:

Id accountId = '0017Q00000U3RvvQAF';

Account account = [
    SELECT Name, Rating, Website, BillingCity
    FROM Account
    WHERE Id = :accountId
    LIMIT 1
];

System.debug(account);

Output:

Account:{Name=Burlington Textiles Corp of America, Rating=Warm, Website=www.burlington.com, BillingCity=Burlington, Id=0017Q00000U3RvvQAF}

SOQL and loops

There’s a very popular way of combining SOQL and loops. I’m not a big fan of it, and you should avoid using it unless you need to increase the performance of your program (heap size). The performance improvement is minor, while the code becomes much more difficult to read. This answer at Stack Overflow summarizes it pretty well (Loop with SOQL or create a List then Loop?). Here’s the syntax: 

for(Sobject iterateVariable : [SELECT Field1, Field2 FROM Sobject]) {
    // loop logic
}

Example #13 – Display all opportunities

for(Opportunity opp : [SELECT Name, Amount, StageName, CloseDate, AccountId
                                             FROM Opportunity
                                             WHERE Amount > 100000 AND 
                                                     StageName = 'Closed Won' AND 
                                                         AccountId != NULL]
) {
    System.debug(opp);
}

This example is completely equivalent to Example #11. The difference is that we can’t reuse the list anymore and the code is now much more difficult to read. We did save a lot of heap size, though.

Binding variables

Instead of hard-coded values, we can use variables in SOQL. This approach is called binding variables, and it’s unique to Apex. We can’t use it in the developer console:

DataType variable1 = value;

List<sObject> sobjects = [
    SELECT Field1
    FROM sObject
    WHERE Field1 = :variable1
];

Example #14 – Binding variables

Integer amount = 100000;
String stageName = 'Closed Won';

List<Opportunity> opportunities = [
    SELECT Name, Amount, StageName, CloseDate, AccountId
    FROM Opportunity
    WHERE Amount > :amount AND StageName = :stageName
];

Here:

• amount: An integer value that’s bound to the Amount field on the opportunity.
• stageName: A string value that’s bound to the StageName field on the opportunity.

This code will create a list of opportunities in the “Closed Won” stage with an amount greater than 100,000.

SOQL Relationships

SOQL for Child to Parent

“Child to Parent” may sound complicated, but the concept behind it is fairly simple—especially if you’re already familiar with cross-object formulas in Salesforce. When we have a lookup or master-detail relationship on one of the objects, we can get any field value from its parent.

SELECT LookupField1.Field1, LookupField2.Field2
FROM Sobject

Still confused? Let’s look at some examples.

Example #15 – Opportunities

Every opportunity has an account record. We can query any account field from the query on the opportunity:

SELECT Name, Account.Name, Account.Email
FROM Opportunity
LIMIT 15

Example #16 – Three levels

We can go up to 5 levels deep with these lookup relationships:

List<Opportunity> opportunities = [
    SELECT Id, Name, Account.Name, Account.Owner.Name
    FROM Opportunity
    LIMIT 3
];

for(Opportunity opportunity : opportunities) {
    System.debug('Owner\'s name: ' + opportunity.Account.Owner.Name);
}

Output

Owner's name: Igor Kudryk
Owner's name: Igor Kudryk
Owner's name: Igor Kudryk

SOQL for related records

The official name of this concept is “SOQL for Parent-to-Child relationships,” but what does that mean exactly? In Salesforce, you have a tab with related objects:

If you go to each individual record from this list, you’ll see that there’s an Id pointing to our main opportunity.

We can query this opportunity with all the related records in just one SOQL query. In this case, parent = the main opportunity, while children = related opportunity products.

SELECT Field1, Field2, (SELECT Field1, Field2 FROM relatedsobjects)
FROM MainSobject

As always, let’s look at the examples.

Example #17 – Related Opportunity Line Items

SELECT Name, StageName, (SELECT Name, ListPrice FROM OpportunityLineItems)
FROM Opportunity

Output in the developer console:

Notice the name that we use to get related records: “OpportunityLineItems.” For each object, the name will be different. You can find the exact name that you should use by going to Setup → Opportunity Product → Fields & Relationships → Opportunity → Child Relationship Name. 

If you’re querying custom objects, you’ll need to append __r at the end.

Example #18 – Related records to Account

This query will give back the opportunity with the requested id and all related opportunities.

SELECT Name, Rating, Website, BillingCity, 
       (SELECT Name, Amount FROM Opportunities), 
       (SELECT Title, Email, Phone FROM Contacts)
FROM Account
WHERE Id = '0017Q00000U3RvvQAF'

Here:

• Name, Rating, Website, BillingCity: Fields to display from the Account records.
• (SELECT Name, Amount FROM Opportunities): Sub-query to get the list of related opportunities to the main account.
• (SELECT Title, Email, Phone FROM Contacts): Sub-query to display the account.
• FROM Account: Main sObject.
• WHERE Id = ‘0017Q00000U3RvvQAF’: Only an account with this Id will be included in the result.

The output:

Example #19 – Related Opportunity Line Items in Apex

List<Opportunity> opportunities = [
    SELECT Name, StageName, (SELECT Name, ListPrice FROM OpportunityLineItems)
    FROM Opportunity
];

for(Opportunity opportunity : opportunities) {
    System.debug('We are in the opportunity with the name: ' + opportunity.Name);

    List<OpportunityLineItem> products = opportunity.OpportunityLineItems;
    Integer numberOfProducts = products.size();
    System.debug('It has ' + numberOfProducts + ' related products.');
    for(OpportunityLineItem product : products) {
        System.debug('Product name: ' + product.Name);
    }
}

This example is a bit more complex because we have nested loops. However, we’re just looping over each opportunity line item per opportunity that we received from the SOQL query. If there are no products on the opportunity, the loop won’t even start.

In my org, only one opportunity has products, so I’m not going to show the whole output—only a part where the loop goes over the related products.

We are in the opportunity with the name: Burlington Textiles Weaving Plant Generator
It has 5 related products.
Product name: Burlington Textiles Weaving Plant Generator GenWatt Diesel 1000kW
Product name: Burlington Textiles Weaving Plant Generator GenWatt Diesel 200kW
Product name: Burlington Textiles Weaving Plant Generator SLA: Bronze
Product name: Burlington Textiles Weaving Plant Generator Installation: Industrial - High
Product name: Burlington Textiles Weaving Plant Generator GenWatt Diesel 10kW

Example #20 – Related records with Apex

Let’s look at a typical combination of what we’ve discussed so far. Note that in this case, we have not one but two related lists! That’s because the account has two related sections: opportunities and contacts. We can have up to 20 sub-queries, but if you need that many, you should rethink your data model.

Id accountId = '0017Q00000U3RvvQAF';
Account account = [
    SELECT Name, Rating, Website, BillingCity, 
           (SELECT Name, Amount FROM Opportunities), 
           (SELECT Title, Email, Phone FROM Contacts)
    FROM Account
    WHERE Id = :accountId
    LIMIT 1
];

List<Contact> contacts = account.Contacts;
List<Opportunity> opportunities = account.Opportunities;

System.debug('Related contacts: ');
for(Contact contact : contacts) {
    System.debug(contact);
}

System.debug('Related opportunities: ');
for(Opportunity opportunity : opportunities) {
    System.debug(opportunity);
}

Remember, the output will depend on the data in your org:

Related contacts:
Contact:{AccountId=0017Q00000U3RvvQAF, Id=0037Q00000JF2LOQA1, Title=VP, Facilities}
Contact:{AccountId=0017Q00000U3RvvQAF, Id=0037Q00000TjSnRQAV, Title=DevOps}
Contact:{AccountId=0017Q00000U3RvvQAF, Id=0037Q00000TjSnbQAF, Title=Head of Sales}
Related opportunities:
Opportunity:{AccountId=0017Q00000U3RvvQAF, Id=0067Q00000BDIMAQA5, Name=Burlington Textiles Weaving Plant Generator}

Dynamic SOQL

You can run an SOQL query from a string as well:

List<Sobject> sobjects = Database.query('SELECT Field1, Field2 FROM Sobject');

As a rule of thumb, you should try to avoid dynamic SOQL as much as possible. The reason for that is bad code readability and significant vulnerability. There are a few cases where you have to use it, but this usually happens once you move to more advanced topics such as Batch Apex. 

Therefore, we’ll go over a quick example and move on.

Example #21 – Dynamic Account fields

String field1 = 'Name';
String field2 = 'Email';

String query = 'SELECT ' + field1 + ',' + field2 + ' FROM Contact';
List<Contact> contacts = Database.query(query);

System.debug('The size of contacts is: ' + contacts.size());
for(Contact contact : contacts) {
    System.debug(contact);
}

Again, this depends on your org’s data:

Working with Dates

A common scenario in Apex is needing to query records that were created a certain time ago (e.g. only this year). Salesforce kindly provides us with shortcuts to work with dates called “Date Literals.” You can find them in the official documentation. Some of the most common are:

• TODAY
• LAST_WEEK
• LAST_MONTH
• LAST_N_MONTHS:N
• NEXT_90_DAYS

Example #22 – All opportunities this week

SELECT Name, StageName, CreatedDate
FROM Opportunity
WHERE CreatedDate = THIS_WEEK

Example #23 – All opportunities in the last 18 months

SELECT Name, StageName, CreatedDate
FROM Opportunity
WHERE CreatedDate = LAST_N_MONTHS:18

Example #24 – All opportunities that will be closed in the next 90 days

SELECT Name, StageName, CreatedDate
FROM Opportunity
WHERE CloseDate = NEXT_90_DAYS

Aggregate functions

We can perform simple calculations with SOQL via aggregate functions. There are five main aggregate functions in SOQL:

• COUNT(): Returns the number of rows in the query result.
• AVG(Field): Returns an average of a field over all the records in the query result.
• MIN(): Returns the minimum value of a field in the query result.
• MAX(): Returns the maximum value of a field in the query result.
• SUM(): Returns the sum of a field in the query result.

I won’t go over this topic because it requires a much more advanced understanding of SOQL. You can find a good explanation and examples in the official Salesforce Documentation.

What we didn’t cover

• SOSL: Even though this article is about SOQL, you often see SOSL mentioned in the same breath. I can save you tons of time: SOSL is useless. I’m sure there are many senior developers with 10+ years of experience who have never written a single line of SOSL, and I’m quite sure you won’t need to either.
• ORDER BY: Simply orders the records in the result query. Use it when you need to order your elements in an SOQL query in a specific way.
• GROUP BY: Groups records based on a field. This is a more advanced topic and rarely used in the real world.

SOQL practice

Theory is cool, but practice is more important. When you run these queries, they might deliver zero records because you just don’t have the data in your org. Can you come up with more tasks? Let me know and I’ll include them!

1. Write an SOQL query to retrieve all accounts with a billing state of “California” and a billing city of “San Francisco.”
2. Write an SOQL query to retrieve the name and phone number of all contacts associated with accounts that have a type of “Customer – Direct.”
3. Write an SOQL query to retrieve the names of all active users.
4. Write an SOQL query to retrieve all cases with a status of “New” that were created in the last 30 days.
5. Write an SOQL query to retrieve all accounts with a rating of “Hot” and an industry of “Technology.”
6. Write an SOQL query to retrieve all opportunities with a close date in the current fiscal year and a stage of “Closed Won.”

SOQL practice – Answers

1. SELECT Id, Name FROM Account WHERE BillingState = ‘California’ AND BillingCity = ‘San Francisco’
2. SELECT Name, Phone FROM Contact WHERE Account.Type = ‘Customer – Direct’
3. SELECT Id, Name FROM User WHERE IsActive = true
4. SELECT Id, CaseNumber, Status, CreatedDate FROM Case WHERE Status = ‘New’ AND CreatedDate = LAST_30_DAYS
5. SELECT Id, Name, Rating, Industry FROM Account WHERE Rating = ‘Hot’ AND Industry = ‘Technology’
6. SELECT Id, Name, CloseDate, StageName FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR AND StageName = ‘Closed Won’

Links

• Beginner SOQL (SFDC99).
• Write SOQL Queries (Trailhead).
• SOQL Basics | Developer Quick Takes (YouTube).
• SOQL whole syntax (Salesforce Docs). Just take a quick look for a few minutes.
• SOQL Cheat Sheet (Salesforce Ben).
• Loop with SOQL or create a List then Loop? (Stackoverflow)

admin

Related Posts