Welcome back to another episode of “Cooking with Code” where I introduce delectable bite-sized topics related to coding in Salesforce.

Today, we are continuing our satisfying sampler of SOQL with Part Two of this three part series.

My first post introduced the basics of SOQL statements, including these concepts and keywords: SELECT, FROM, LIMIT, ORDER BY, basic WHERE conditions, and basic operators.

This, my second post, will show how we can pull data from related standard and custom objects using dot notation and subqueries.

In my third and final post I will delve deeper still, into aggregates, advanced operators and wildcards, working with dates and numbers, and more advanced WHERE conditions.

So let’s dive in and build on what we’ve learnt about SOQL.

So what about querying related tables?

The joy of SOQL is that not only does it let you query a single object, but it also lets you query any related objects. How cool is that!

As a Salesforce admin, you know how to create formula fields that pull data from a parent field into the child. You also know that you can aggregate data from children up to a parent record.

SOQL has ways to pull data from children to parents and vice versa. Let’s tackle the easiest type of relationship direction first, pulling data from a parent to a child.

Finding parent object field values using standard relationships

The code snippet below shows how we could grab the account name along with the contact name for any contact whose email address is ‘[email protected]’.

SELECT name, contact.account.name FROM contact 
WHERE email = '[email protected]'

I want to point out a few things that are going on here…

Dot Notation

When you’re looking for data in the parent object (or grandparent, or great-grandparent, etc.), you use something called “dot notation.” This is how we say which related object we want to travel towards from the contact. In our case we’re interested in the account name, so that would be:

contact.account.name

In plain English we could say, “Start at the contact, move up to its account and grab the name field within the account.” Note that we always start with the object that we reference in our FROM clause:

childObjectName.parentObjectRelationshipName.fieldName

This is how we work with “out of the box” standard Salesforce relationships. But does this change when we’re looking at custom objects? So glad you asked!  It kind-of changes…

Finding parent object field values using custom relationships

The good news is that we use the basically the same syntax when dealing with custom relationships as with standard relationships. To get to a related object you go through the relationship name. For standard relationships that happens to be the same as the standard object name, but for custom relationships (even those between standard objects), you use the API name of the relationship field, but with __r rather than __c.*

* This is rather clever of Salesforce, as it ensures that nothing will break if they happen to add a standard object with the same name as our custom object.

Let’s look at an example…

Imagine our contact object includes a custom lookup field to reference the contact’s favorite recipe.  (I couldn’t resist putting a cooking reference in here somewhere!).

When we created the lookup relationship, we used the API field name of recipe__c, and therefore the relationship API name will be recipe__r.  (Get it? “R” for relationship.)

Let’s look at a visual for how our recipe__c object relates to both Contacts and Accounts:

Click to enlarge

If we wanted to pull out the contact’s name, their favorite recipe, and how hard that recipe is to create, then our code would look like this:

Looking at the code, you might be asking yourself, “Why wouldn’t you just use contact.recipe__c for the recipe name? Isn’t that what we see when we look on the contact record?”

Not really. If you exported the contact record, what’s actually being stored in this custom field is the ID of the recipe. If we want the actual name, we have to travel up the relationship from the contact to the recipe and get the name.

Are there limits on relationships??

Great question and yes there are. You can have up to 20 relationships specified within a single query, and within each relationship, you can have up to five levels. This might sound limiting, but it’s actually a lot. For example, the following would be only three levels (trick – count the “dots” and that’s how many relationship levels you are spanning):

More information on limits is on the Relationship Queries Salesforce documentation page.

So now that we know how to look for data in an object, and related parent objects (both custom and standard), what about moving down the relationship path from a parent to its children.

Finding child object data from a parent (using sub queries)

There are many reasons why you would want to pull data from the child or children related to a parent. Many of these have analogies to various types of Salesforce reports.

Just as the reports above pull data using the Salesforce Report Builder, so too can we pull the same data using SOQL. We’ll work with Scenario A and B in this blog post, and save Scenario C for the next.

Scenario A: Pulling data from the child to the parent object

Salesforce Report Builder Analogy:

Equivalent SOQL Code:

SELECT name, (SELECT firstName, lastName, email FROM contacts)
FROM Account
WHERE name = 'Baking Supply Co'

This would result in the following:

Phew! Take a deep breath, and repeat after me “I am okay…I can do this.” Now let’s tackle this piece by piece.

You can probably see that there is a difference in the SOQL result set and the report result set. The report returns two rows (one for each contact), while the SOQL returns only one row. If you look carefully at the SOQL results, you can see that while there is only one row (for the single account), that row’s “Contacts” column contains the data for both the contacts related to the Baking Supply Co account.

Now let’s look at the code…

Lines 2 and 3 should look pretty standard by now, they are asking the query to pull data FROM the account object, WHERE the name field is equal to “Baking Supply Co.”

The first part of line 1 is also pretty straightforward: select the account name.

But what are we doing with the second part of line 1?
We’re running what is called a “subquery”. It is SELECTing the firstName, lastName and email fields FROM the contact, and displaying them along with the other field we’ve included in the first part of the select clause.

A few things to know about subqueries:

• We don’t use dot notation, because that only works from the child to the parent, not from the parent to the child.
• For standard relationships, we use the plural version of the subquery object name (e.g., contacts instead of contact).
• For custom relationships, this could pretty much be anything, but it will be whatever is set up in the Child Relationship Name within the Field Definition.
• The subquery is surrounded by parenthesis.
• There is a comma between all the account fields and the subquery.

Let’s move on to the second scenario…

Scenario B: Filtering a parent result set with data from the child

Salesforce Report Builder Analogy:

Equivalent SOQL Code:

SELECT name, industry FROM account
WHERE id IN
(SELECT accountId FROM opportunity WHERE stageName ='closed won' AND amount>100)

This would result in the following:

Again, deep breaths and we’ll step through this together.

Line 1 is pretty standard, we are SELECTing the name and industry fields from the account object.

Line 2, also pretty standard, we’re adding a filter that is asking for just the accounts WHERE the id of the account is included IN a list.

Remember back to Part One in this series, where we used IN to pull out all accounts within the western states? This is the same kind of IN.

But Yikes! What are we doing in line 3?

This is just like the first IN statements we saw, but instead of hard-coding the values into the list, we’re pulling them from the database using a subquery.

The subquery is doing the following:

• SELECTing the accountID
• FROM the opportunity object
• WHERE the opportunity stageName is “Closed won” AND the opportunity amount is greater than 100.

Once that subquery runs, it will automagically return a comma delineated list of account IDs that we are then using to compare the ID for the account object in line 2.

Neat-o!

Wrapping Up

Phew! You’re really getting into some awesome SOQL stuff. Let’s summarize what we we’ve learned from Part 1 and 2:

• SOQL is used to pull data out of Salesforce Objects (also known as “querying the database”).
• Like Apex, for the most part, SOQL is not case sensitive.
• There are only two required keywords for every SOQL statement:
  • SELECT – Which fields do you want to see?
  • FROM – What object are you pulling from?
• There are a number of optional keywords, including:
  • LIMIT – How many records do you want to pull?
  • ORDER BY – What sort order do you want? ASCending or DESCending? If you don’t specify an order, then SOQL will assume ascending.
  • WHERE – How do you want to filter the results?
• Within WHERE clauses, we can use a number of operators, including:
  • Equals (“=”) and Not Equal To (“!=”)
  • IN – Used to filter by a comma separated list of values
  • AND – Used to combine multiple filters; both sides of the AND must be true
  • OR – Used to combine multiple filters; only one side of the OR needs to be true
• We can select to query fields from a single object, and any objects that are related to it (for up to 20 relationships per query, and up to 5 levels within a single relationship):
  • We use Dot Notation to move from a child up to a related parent object.
  • We use Subqueries to move from a parent down to a child relationship.

We’re officially at the end of Part Two of our intro to SOQL and I hope that it was satisfying; not too salty, not too sweet, and left you wanting more!

Don’t worry! Next week I’ll be giving you the third and final part of “A Sweet Intro to SOQL,” where I’ll tempt you with some more sassy SOQL concepts, like aggregates, advanced operators and wildcards, working with dates and numbers, and some more advanced WHERE conditions.

Until next time…see you in the cloud coding kitchen!