Basic SOQL Relationship Queries
Developers often need to answer questions like “Who are all the contacts for each account?” These usually lead to more complex questions like “When was the last time we visited key contacts from our top revenue generating accounts and who did we visit?”
Understanding SOQL relationships and how to query them can help you answer these questions in code. Today I’m going to show you three primary examples. You can always read more details in the SOQL API guide. For the queries used in this post I’m going to be using Account and Contact objects. Keep in mind the same principles apply to both standard and custom objects. All of the examples can be run in the Query Editor tab of the Developer Console.
If you’re familiar with SQL the first thing you might want to do is join the two tables and run a query against their fields. SOQL supports this through relationship queries. Relationship queries, established by look up and master detail fields, allow developers to retrieve data through parent-child and child-parent relationships. Relationship queries can be bi-directional: from a child object looking at a single parent or from a parent object looking at zero or more children. The cardinality of a relationship query has a large impact on SOQL construction as we’ll soon see.
Let’s take a look at a simple relationship query. Here we answer the question “What is the name of our Accounts and which user owns them?”
Child to Parent Query:
SELECT Name, Owner.Name FROM Account
Earlier I stated that cardinality was important to the structure of the SOQL. Here we are at the child querying the parent so we use the relationship field ‘owner’ then we get the field ‘name’. We can say Owner.Name because for each Account record there will be at most one owner with one name. You can think of the relationship field as kind of like a pointer to the related object. We can then use that pointer to access the fields of the related object. It’s the “many” looking at the “one” that allows us to use the previous syntax. You’ll also want to remember that you can only go one level down in relationships when querying from parent to child and five levels up the relationship chain when querying from a child to a parent.
What if we wanted to change direction and thus change the cardinality? In this query we answer the question “Who are the contacts for each Account?”
Parent to Child Query :
SELECT Account.Name,(SELECT Contact.Name FROM contacts) FROM Account
Notice how the SOQL syntax has changed. Since there are now zero or more possible Contacts for each Account we can no longer use the relationship field to point to a single Account like we could for Owner in our first query. We need to use a nested select to build a result set that contains all the related Contacts. For each Account we match all the related Contacts with this syntax. There’s a couple other things worth noting. First, we are using the relationship name ‘contacts’ to refer to related child records. Second, I prefaced each “name” field in the query with Account and Contact. This isn’t necessary but a best practice to remove ambiguity when reading the statement.
If you ran that last query in the developer console on a Developer Edition of Salesforce you probably noticed something interesting. The query returned all Account records whether they had a related Contact or not. This brings up an interesting point. By default relationship queries behave like an outer join. What can a developer do if they want to create and inner join on these two objects? The following SOQL will do the trick
Create an Inner Join:
SELECT Account.Name, (SELECT Contact.Name FROM contacts) FROM Account WHERE Account.Id IN (SELECT Contact.accountId FROM Contact)
Here, we use the where clause to match only records where the AccountId exists in the list of matching AccountIds in the related Contacts. Since we are filtering on an indexed field we also make the query a bit faster. This is also an example of a selective query (see below).
Using these basic principles you can start to build more complex SOQL queries. As an example of a more complex query we can refer to the second question mentioned above: “When was the last time we visited key contacts from our top revenue generating accounts and who did we visit?”
More Complex Query:
SELECT task.account.name, task.who.name, task.activitydate FROM task WHERE task.activitydate <= TODAY AND task.who.type = 'Contact' AND task.account.annualrevenue > 150000000 ORDER BY task.account.annualrevenue DESC
I’ve introduced two fields in this example which haven’t been mentioned yet. . These are the polymorphic fields who.name and who.type. Polymorphic fields are special field types that can point to different objects depending on the type of relationship. I’ll have more to say about polymorphic queries, selective queries and aggregate queries in follow on posts. Also note you can use dynamic date functions as a part of your SOQL. In this latest query I’ve used TODAY as a part of the filter criteria.
If you’ve been perplexed or frustrated with understanding how relationship queries work on the platform hopefully now you have a better understanding of how they work. I’ll be exploring them and some interesting ways they can be used in future blog posts so stay tuned.