Retrieve, Modify, Persist – Part 3

Relationship Queries

Relationships. They are such an important part of the human condition, or any type of life for that matter. We start creating relationships the moment we are born, and even before that. Our brain starts developing connections, and sometimes they are very simple connections. Other times, they are very complex. Without these connections, our bodies would be at a loss.

The same degree of relationship building happens everyday in our Salesforce environments. It begins the moment we define a relationship field (Lookup or Master-Detail), and continues each time a User creates a new record and uses the relationship field to link it to another.

SOQL — it’s our ally when it comes to using those relationships to our advantage. The reason behind this is because Apex is not only aware of the objects and fields we create in our Orgs, but also the relationships between them. Let’s dive into how we can benefit from these connections.

First things First

In Salesforce, there are two main types of relationships:

  • Standard Relationship: these are defined by Salesforce and come out-of-the box the moment you receive a Salesforce environment. They mainly exist between Standard Objects. Examples of these relationships include: Account & Contact, Opportunity & Opportunity Line Item, Account & Cases, Account & Opportunity.
  • Custom Relationship: These are defined by you or another system administrator. These relationships can exist between any type of objects.

Parent-to-Child Relationship

Whenever you have a Lookup or a Master-Detail field connecting two objects together, you create a parent-to-child relationship, also known as a One-to-Many (1:n) relationship. These relationships are very similar to real-life scenarios — a Parent can have one or many children and all of the siblings share something in common — their parents.

Let’s say you want to retrieve all of the contact IDs related to accounts in the “Energy” Industry. Here is how you do it:

SELECT Name,(Select Id FROM Contacts) FROM Account
WHERE Industry = ‘Energy’

This image shows the result for the SOQL Statement above in my Developer Org:

It’s Quiz Time:

  • How many SOQL statements were issued?
  • How many records were returned?

If you answered 2 and 3, respectively. I’m sorry to tell you: you are wrong. The correct answers are 1 and 10. Don’t believe me? Let’s take a closer look at the debug log for the statement:

The Reason

The query section colored above in blue is called a subquery. Here are some key items to understand about subqueries:

  1. Subqueries take advantage of a Parent-to-Child connection by using the plural Relationship Name.
  2. They are always enclosed in parenthesis.
  3. They do not count against the SOQL Governor Limit.
  4. They do count against the Limit of records returned by a SOQL Statement.

If you are still wondering why 10 rows are returned, maybe this will help:

Both, the Accounts and Contacts rows returned are aggregated, and count against the Governor Limit.

Child-to-Parent Relationship

There are always two sides of a relationship. Child-to-parent relationship queries detail when a Child record reaches out to the Parent record to access its value. For example, say you are looking at a Case, and you also need to know the Name and Phone Number of the Account associated with this Case. Here is how you would perform this search:

SELECT CaseNumber, Account.NameAccount.Phone FROM Case
WHERE CaseNumber = ‘00001000’

To access fields of a Parent Record, we use the singular Relationship Name and dot notation inside the SOQL statement as colored in blue in the preceding example. We can also filter using dot notation as follows:

SELECT CaseNumber, Account.NameAccount.Phone FROM Case
Account.Name = ‘Edge Communications’

For Child-to-Parent relationship queries, rows returned are not aggregated. In the first example, we showed that the number of records returned is one. The maximum number of relationships you can scale up to is 5.

Relationship Names

So far, we’ve talked about plural and singular relationship names. The main difference between them is direction.

Standard Relationships:

If your query is looking down, denoted by the purple solid line (from the Parent to the Children), you need  to reference the plural relationship Name.

If your query is looking up, denoted by the yellow dotted line (from the Children to the Parent) you need to reference the singular relationship Name with dot notation.

 


Custom Relationships:

If your query is looking down, denoted by the purple solid line (from the Parent to the Child), you need  to reference the plural relationship Name with the “__r” suffix.

If your query is looking up, denoted by the blue dotted line (from the Child to the Parent), you need to reference the singular relationship Name with dot notation with the “__r” suffix.

 

 

 


Complex relationships

Now that we understand the more basic relationships. It’s time to query more complex scenarios. You might refer to the next section as a cheat sheet of relationships. Because, at the end of the day, who doesn’t like cheating?


Left Inner Join

You want to query Parent records that have at least one Child:

SELECT Name FROM ParentOblesct_c WHERE
Id IN (Select parentRelationShipField_c from ChildObject_c)


Right Inner Join

You want to query Child records with a Parent:


SELECT
 Name FROM cildObject_c WHERE
parentRelationShipField_c != NULL


Right Anti-Join

You want to query Child records without Parent:


SELECT
 Name FROM cildObject_c WHERE
parentRelationShipField_c = NULL

 

Left Anti-Join
You want to query Parent records without Children:


SELECT
 Name FROM ParentOblesct_c WHERE
Id NOT IN (Select parentRelationShipField_c from ChildObject_c)

 

Now you’ve mastered SOQL Relationships and you are ready to take advantage of the way your data connects. Stay tuned for a post on SOSL, a different type of query language!

 

 

About the Author

Clara Perez is a Salesforce MVP and Lead Developer at Great Wave who loves teaching Salesforce concepts.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn