How to write a cross-object SOQL query (“Downwards traversal”)

Introduction

Cross-Object SOQL query is an advanced topic in SOQL. We use this topic when we have relations between the objects. It’s something like querying a chain of objects which are related to each other. We generally use more of these Cross-Object queries in salesforce by the developers.

Before we start with the query it will be good to understand the related objects first. Suppose we have an object that is called Account and it contains account details of all the employees. Now, we have another object that is called Contact that was created based on the reference of Account. Now, the Account here is known as Parent Object and Contact here is known as Child Object. It means all the data that is within Contact is something related to Account. All the master-detail objects too come under the category of “Parent-Child”.

Parent-to-child relationship

Downwards traversal

Here, in Downward Traversal, we query the parent object with the help of its child object. That’s why it is known as Parent-to-child relationship. We choose the Parent object to query the fields from and for its related object fields, we do a query over child object within brackets. In this way, you can see that the final result of the query will contain data from both the objects. Let’s see how it works in the below example to understand this clearly.

Syntax:

SELECT <field from parent>, (SELECT <field from child> FROM < relationshipName>) FROM <Parent Object>

Or

SELECT <field from parent>, (SELECT < Child Object >.<field from child> FROM <Parent Object>.< relationshipName>) FROM <Parent Object>

 

Example

  • SELECT Name, Phone, (SELECT LastName FROM Contacts) FROM Account Limit 5

In this example Account is the parent object, the parent object has a name for the child relationship (here, relationshipName ) that is unique to the parent, and it is the plural of the child object name (here. Contacts). Contact is the child object. As mentioned earlier we pull the related objects by writing a separate query in the brackets. Now along with Name and Phone we can also see Last Names of related contacts in the output. Observe that all related contacts’ last names will be in a list.

 

Output

The output for the above query,

cross-object SOQL query (“Downwards traversal”)

Explanation

There are three columns in the output as we selected three fields in the query from the Account object. Here, the first 2 columns are from the parent object and the other one is from child. This is because we transverse from Parent to Chile for the 3rd field.

Important Note

We can also write another query again in brackets now to pull the from its child object field. That looks like a chain of child objects. Before performing this just check the related object for the Parent object and write the API names. Write the code in the Query editor in developer console. This might look a bit complex. In this use case, the concept of “__c” and “__r” comes into the picture. To learn about that, read here.

Reference

https://www.sfdc99.com/2013/06/24/example-how-to-write-a-cross-object-soql-query-part-2/

https://www.salesforcetutorial.com/relationship-queries-salesforce/

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.