SOQL: How to use TypeOf

Salesforce recommends using Contact Point objects to store contact information when you need to manage multiple types of contact details for a single entity. This approach allows you to leverage the one-to-many relationship between each Contact Point object and its associated parent object, providing a flexible and scalable way to handle various contact methods.

The Contact Point objects are:
- Contact Point Email (ContactPointEmail)
- Contact Point Phone (ContactPointPhone)
- Contact Point Address (ContactPointAddress)

One trade-off of using these Contact Point objects is the polymorphic ParentId field that links them to their parent objects. A polymorphic field is a reference (lookup) field that can reference one of many possible objects.

Two other well-known polymorphic fields are WhatId and WhoId on the Task object, used to relate the task to an individual (WhoId) or another object (WhatId).

One issue with polymorphic fields is that we cannot always traverse the relationship using the usual dot notation in a SOQL query. While Id, Name, and Type (plus FirstName & LastName on Who fields) are available through the dot notation:

SELECT Id, Name, EmailAddress, Parent.Id, Parent.Name FROM ContactPointEmail

If we were to need a different field, e.g., Description:

SELECT Id, Name, EmailAddress, Parent.Id, Parent.Name, Parent.Description FROM ContactPointEmail

This would result in a No such column ‘Description’ error.

Obviously, we could split this into two queries, but a likely better solution is to use the TYPEOF keyword. TYPEOF is an optional clause that can be used in a SELECT statement of a SOQL query when you’re querying data that contains polymorphic relationships. A TYPEOF expression specifies a set of fields to select that depend on the runtime type of the polymorphic reference.

SELECT Id, Name,
  TYPEOF Parent
    WHEN Account THEN Id, Name, Description
    WHEN Individual THEN Id
  END
FROM ContactPointAddress

Looking at the query syntax there are four parts when using TYPEOF.

TYPEOF: This specifies the polymorphic field

WHEN: This is the object type checked against the polymorphic field during the SELECT execution (also consider an ELSE for if no match to the object is made)

THEN: Here the fields to be retrieved are specified

END: Concludes the TYPEOF statement

It suggests that TYPEOF is returned as another field; however, the values returned cannot simply be referenced as a field. Also, if we are just running the query in the developer tool query editor, it won’t even show the output (although some other tools will).

So, let’s look at how we use the values in Apex:

List<ContactPointAddress> addList = [SELECT Id, Name,
  TYPEOF Parent
    WHEN Account THEN Id, Name, Description
    WHEN Individual THEN Id
   END
 FROM ContactPointAddress LIMIT 10];

for(ContactPointAddress add: addList) {
 if(add.Parent instanceof Account){
   Account acc = add.Parent;
   System.debug(acc.Description);
 }
}

You can see that after assigning the SOQL query to a list, when we iterate through the list, we are using the `instanceof` keyword to determine the type of the parent object. With the object determined, we can access the fields we have queried.

Performance Considerations

While polymorphic fields offer flexibility, they can also impact query performance. It’s important to optimize your queries and consider the performance implications, especially in large datasets.

Error Handling

When working with polymorphic fields and TYPEOF in SOQL queries, it’s crucial to handle potential errors gracefully. Ensure that your code includes proper error handling to manage scenarios where the expected fields or data types are not available.

SOQL: How to use TypeOf

Performance Considerations

Error Handling

Further Reading