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

Introduction

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

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”.

child-to-parent relationship

Upwards traversal

Here, in Upwards Traversal we query the child object with the help of its parent object. That’s why it is known as child-to-parent relationship. We choose the parent object to query fields using dot notation with the help of a relationshipName property that is holding the reference to the parent object. 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.
Syntax:

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

Example

Here we go with a simple example to understand the upwards traversal of the SOQL query

  • SELECT Name, Account.Name, Account.Phone FROM Contact LIMIT 5

Here, Account in the Contact query is the parent object and Contact is the child object. To call fields from parent object via a child we use dot notation as shown above. Also, the relationshipName here is same as the parent object name i.e. Account.

Output

Now when you enter this query in the query editor we get the following result as output…

cross-object SOQL query (“upwards”)

Explanation

There are three columns in the output as we selected three fields in the query from the Contact object. But here the first field is from Contact and the other two are from the Account object. That is because we transverse from Contact to Account object using dot notation as Contact is a child of Account. And if you observe we got only 5 records because we set the limit there.

Important Note

In the same way, we can also create a chain to those parent’s objects which is a complex topic. We can also transverse to the parent’s parent object by using again dot notation as an extension. By using this you can retrieve any field from any object when they are related to each other but not some other objects, anyway, you have the requirement of retrieving fields only when they are related to each other. 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/09/example-how-to-write-a-cross-object-soql-query/

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

Leave a comment

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