SOQL Relationship Queries

SOQL Tutorial What is SOQL SOQL Real Life Scenario SOQL Naming Conventions SOQL WHERE SOQL Comparison Operators SOQL Logical Operators SOQL ORDER BY SOQL LIMIT SOQL OFFSET SOQL Aggregate Functions SOQL GROUP BY SOQL HAVING SOQL Relationship Queries SOQL Date Literals SOQL Date Functions SOQL History Objects SOQL Sharing Objects SOQL in APEX Apex Variables in SOQL Queries Static and Dynamic SOQL SOQL return types SOQL Governor Limits Efficient SOQL Queries

A Salesforce relationship is established between 2 objects by creating a lookup or master-detail relationship.

For instance, Appointment__c has two lookups fields Patient__c (lookup on Contact object) and Clinic__c (lookup on Clinic__c object). These are custom lookup fields as we created them only in our Salesforce instance. The object on which the field is defined, here appointment__c, becomes the child object, and contact/clinic__c becomes the parent.

Similarly, we have a field AccountId on Contact establishing a lookup relation from Contact to Account. So in this relationship, Account is the parent, and Contact is the child.

Fig: Relationship between Account and Contact Object.

Now SOQL allows us to access data from child to parent, as well as parent to child. Let’s see how:

SOQL Child-to-Parent Query

To access the parent’s field from the child, SOQL uses dot (.) notation.

Example #1: Fetch all contacts name, as well as account’s name and ID.

SELECT Id, Name, Account.Name, Account.Id
FROM Contact

Example #2: Fetch all the appointments of the patients who have made an appointment in Orlando clinic.

SELECT Id, Patient__r.Name
FROM Appointment__c
WHERE clinic__r.Name = 'Orlando'

Example #3: Find patients name, patients account name who have made an appointment in Orlando clinic.

SELECT Id, Patient__r.Name, Patient__r.Account.Name
FROM Appointment__c
WHERE Clinic__r.Name = 'Orlando'

Notice that we have traversed 2 parent relationships to get the Account Name: Patient__r.Account.Name

Example #3: Find the appointments created by Laura Magson.

Select Id, Name
FROM Appointment__c
WHERE CreatedBy.Name = 'Laura Magson'

What you have to remember:

• The custom lookup relationships are fetched by applying ‘__r’ at the end, and then followed by . and field name. The pattern is : Parent_Object__r.Cutom_Field__c.
• CreatedBy is a default field created automatically and is a lookup to the User Object.
• The maximum number of relationships we can traverse like this is 5.
• If a parent is not found, SOQL returns NULL for that field.

SOQL Parent-to-Children Query

We can fetch child records by using nested queries

Example #1: Find all the accounts, and all the contacts under that account.

Select Id,Name, (Select Id,Name from contacts)
FROM Account

Example #2: Find the clinics in Florida, and also fetch the appointments made for each clinic in Florida.

SELECT Id, Name, (SELECT Id,patient__r.name,clinic__r.name FROM Appointments__r)
FROM Clinic__c
WHERE State__c = 'Florida'

This query returns all the clinics in Florida, and for each clinic, the appointments made for that clinic.

Here is how the query runs in the Workbench.

Fig: Showing how a parent to child query returns results in workbench

What you have to remember:

• SOQL subquery is enclosed in brackets, which is mandatory.
• The subquery supports WHERE, ORDER BY and LIMIT clauses like a normal SOQL.
• For Standard Object (like Contact) the parent-to-child relationship is a plural name. For example, the relationship name for the Contact Object is Contacts.
• For Custom Object (like Appointment__c) the parent-to-child relationship is a plural name appended with __r. For example, the relationship name for the **Appointment__c Object is Appointments__r.
• You can find this child relationship name by opening the field from the setup menu in that object.

SQL JOIN VS SOQL Relationships

SQL allows you to combine data from two or more tables. These tables can be any tables, and JOIN can be performed on any column. In Salesforce SOQL, if we want to fetch data from more than one object, there must be a relationship (lookup or master-detail) between the two objects. Salesforce does not have an explicit JOIN keyword.

Let’s assume an SQL database that has two tables:

• Account table with two columns Id and Name
• Contact table with 4 columns Id, FirstName, LastName and AccountId

Let’s say we want to fetch all contacts and also the account’s name, which is a child to parent query we discussed earlier.

In SQL, we will have to perform a Right Outer Join:

SELECT c.Id, c.FirstName, c.LastName, a.Name
from Account a
Right Join Contact c ON (c.AccountId = a.Id)

The equivalent SOQL query would be:

Select Id, FirstName, Lastname, Account.name
from Contact

Thus child to parent query corresponds to a right outer join in this case.

As you could see, the SOQL is more intuitive to read. Note that even if field AccountId is null, the records will be fetched in SOQL.

Now, let’s see how we can implement a Left Outer Join in SQL:

Select c.Id,c.FirstName,c.LastName,a.Name
from Account a
LEFT Join Contact c ON (a.Id = c.AccountId)

This query will fetch all accounts, and also all contacts related to that account.

The equivalent SOQL query would be:

Select Id,Name, (Select Id,FirstName,LastName from contact)
FROM Account

The Parent to child nested query corresponds to a left outer join in this case.

To implement an Inner Join, we can use the IN operator: SOQL query:

Select Id, Name
from account
where Id IN (Select AccountId from contact)

This query will return only those accounts which have at least one contact. We discuss in-depth about the IN operator in subqueries in the next section.