SOQL: Querying Parent and Child Objects

The previous post covered the basics of Salesforce Object Query Language (SOQL). Now, let’s look into one of SOQL’s powerful features: traversing relationships. This allows you to query related parent and child objects, making your data retrieval more comprehensive and efficient.

Understanding Relationships in Salesforce

Salesforce objects can have relationships with each other, similar to how tables in a relational database can be related. There are two main types of relationships in Salesforce:

1. Lookup Relationships: A loose relationship where child records have a reference to a parent record.
2. Master-Detail Relationships: A tighter relationship where child records are strongly tied to a parent record.

Querying Parent Objects

When querying parent objects, you use dot notation to access parent fields from the child object. This is known as a child-to-parent relationship query.

Example: Querying Parent Fields

Suppose you want to retrieve the account name for each contact while retrieving the contact’s name. Here is the proper syntax to do it:

SELECT Contact.FirstName, Contact.LastName, Contact.Account.Name from Contact

In this query, Contact.Account.Name retrieves the Name field from the parent Account object related to each Contact.

Notice the object specified each time before the dot and the field name. Also to access the Account we also use the dot notation from the Contact and then another dot to the field. While this is the specified syntax, we can actually make things a little easier and leave off the Contact. part on each field making it much easier to read.

SELECT FirstName, LastName, Account.Name FROM Contact

This is essentially the same query; Account.Name retrieves the Name field from the parent Account object related to each Contact.

We will continue to use the simpler syntax for the rest of this post.

Example: Filtering by Parent Fields

You can also filter child records based on parent fields. For example, to find contacts whose parent account is in the ‘Technology’ industry:

SELECT FirstName, LastName, Account.Name FROM Contact WHERE Account.Industry = 'Technology'

In child-to-parent relationships, the name of the relationship to the parent is the relationshipName property that references the parent object. For instance, the Contact child object has a child-to-parent relationship with the Account object, meaning the relationshipName in Contact is Account. When we look at custom objects the relationshipName will look a little different. Salesforce uses a special naming convention to reference the parent object from the child object. For custom objects, this relationship name ends with __r. So if the custom object identifier is Project__c the relationship name is Project__r.

SELECT Name, Project__r.Name FROM Task__c

Querying Child Objects

When querying child objects, you use subqueries to retrieve related child records from a parent object. This is known as a parent-to-child relationship query.

In Salesforce, a parent object has unique names for its child relationships, typically using the plural form of the child object name. For example, the Account object has a child relationship with Contact that is named accordingly: Contacts.

Example: Querying Child Records

Suppose you want to retrieve all contacts related to each account. Here’s how you can do it:

Full syntax

SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account

Shortened syntax

SELECT Name, (SELECT FirstName, LastName FROM Contacts) FROM Account

In this query, the subquery (SELECT FirstName, LastName FROM Contacts) retrieves the FirstName and LastName fields from the Contact object related to each Account.

This may initially seem like the same query as when we were querying parent records but it is not.

• Query Parent Records: Will get all contacts and the name of the account they belong to. In this case, If an account exists with no contact, it will not be returned.
• Query Child Records: Will get all accounts and any associated contacts. In this case, If an account exists with no contact, it will be returned.

Example: Filtering by Child Records

You can also filter parent records based on child records. For example, to find accounts that have contacts with the last name ‘Smith’:

SELECT Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Smith')

In parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. In this case; Account has a child relationship to Contact, and the relationshipName is Contacts.

You can combine both child-to-parent and parent-to-child queries to retrieve comprehensive data.

Practical Tips

• Use Selective Filtering: Always filter on indexed fields in both parent and child objects for better performance
• Use Relationship Names: Ensure you use the correct relationship names in your queries. You can find these in the Salesforce Object Manager under the object’s fields and relationships.
• Limit Subqueries: Be mindful of governor limits when using subqueries, as they can impact performance.
• Test in Developer Console: Use the Salesforce Developer Console or tools like Salesforce Inspector to test and refine your queries.

---

Conclusion

Traversing relationships in SOQL allows you to create powerful and efficient queries that retrieve related data from parent and child objects. By mastering these techniques, you can leverage the full potential of SOQL to meet your data retrieval needs in Salesforce.

For more advanced topics and official documentation, check out Salesforce SOQL Documentation.