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:
|
1
|
SELECT Name, Owner.Name FROM Account
|
Results
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 :
|
1
|
SELECT Account.Name,(SELECT Contact.Name FROM contacts) FROM Account
|
Results:
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.
Join Type
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:
|
1
|
SELECT Account.Name, (SELECT Contact.Name FROM contacts) FROM Account WHERE Account.Id IN(SELECT Contact.accountId FROM Contact)
|
Results:
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:
|
1
|
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 BYtask.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.
No comments:
Post a Comment