Simplifying SOQL with Polymorphic Relationships

Hello Trailblazers,

In this post, we're going to learn about TYPEOF clause in SOQL using which we can query data that contains polymorphic relationships. I recently came across this Stackoverflow Question which was asked 7 years 3 months ago considering the time I am writing this post where the user has asked about "Unable to query FederationIdentifier on case owner" The question is pretty old but is still valid. You cannot query case owner's FederationIdentifier using a simple SOQL query on case object. Let's see why?

The Problem

I have a case record in my org as shown below:

Let's say you want to query the case subject, as well as the case owner's FederationIdentifier field. As you can see above, the owner of case is a person named User User and as we open this user's record, you can see below that the Federation ID field has a value 12345.

Well, if I tell you to write a SOQL query in order to get this federation id from case, maybe the first query that comes to your mind is something like this:

SELECT Subject, Owner.FederationIdentifier FROM Case WHERE Id = '5005D000008nxIkQAI'

Now, let's run this SOQL query and see the results:

As you can see above, we're getting this error:

SELECT Subject, Owner.FederationIdentifier FROM
                ^
ERROR at Row:1:Column:17
No such column 'FederationIdentifier' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

This error is coming because the OwnerId field on Case is a polymorphic relationship field which can refer to a Group or a User. In case of a User, we'll have the FederationIdentifier field but in case of a Group, we won't have it. Therefore, we can say that, while you're executing this query, salesforce is not sure if the owner is a Group or a User and therefore, the query could not be executed.

Ok, got the issue now but how to resolve it? Should I create a formula field on case to get the case owner's federation id? You can do it, but let's also see how we can query it!

The Solution

Salesforce has provided an optional TYPEOF clause that can be used in a SOQL query including polymorphic relationships. TYPEOF clause is available in API version 46.0 or LATER. Let's solve our issue first and then we'll see the format of TYPEOF clause in detail. In order to query FederationIdentifier from case owner we can write a query as follows:

SELECT Id, TYPEOF Owner WHEN User Then FederationIdentifier END FROM Case WHERE Id = '5005D000008nxIkQAI' AND Owner.Type = 'User'

As you can see above, I've included the statement TYPEOF Owner WHEN User Then FederationIdentifier END. It's basically saying that when the Case Owner's type is User, then query FederationIdentifier field. I've also added a Type filter for case owner at the end of query: AND Owner.Type = 'User'.  The result for this query is given below:

Starting SFDX: Execute SOQL Query...

07:30:06.358 sfdx force:data:soql:query --query SELECT Id, TYPEOF Owner WHEN User Then FederationIdentifier END FROM Case WHERE Id = '5005D000008nxIkQAI' AND Owner.Type = 'User'
Querying Data... done
 ID                 OWNER                                                              
 ────────────────── ────────────────────────────────────────────────────────────────── 
 5005D000008nxIkQAI {                                                                  
                      "attributes": {                                                  
                        "type": "User",                                                
                        "url": "/services/data/v56.0/sobjects/User/0055D000005BiagQAC" 
                      },                                                               
                      "FederationIdentifier": "12345"                                  
                    }                                                                  
Total number of records retrieved: 1.
07:30:10.900 sfdx force:data:soql:query --query SELECT Id, TYPEOF Owner WHEN User Then FederationIdentifier END FROM Case WHERE Id = '5005D000008nxIkQAI' AND Owner.Type = 'User'
 ended with exit code 0

As you can notice above, we're getting the related Owner record with FederationIdentifier field in response. Let's say you want to get the FederationIdentifier using this query in apex, you can refer to the below code:

List<Case> caseList = [SELECT Id, TYPEOF Owner WHEN User Then FederationIdentifier END FROM Case WHERE Id = '5005D000008nxIkQAI' AND Owner.Type = 'User'];
User caseOwner = caseList[0].Owner;
System.debug(caseOwner.FederationIdentifier);

You'll get the FederationIdentifier from case owner in the debug as shown below:

Awesome! Now that we've solved the issue, let's learn more about the TYPEOF clause. The TYPEOF statement in SOQL is of the format:

TYPEOF <PrimarySObjectField> WHEN <PossibleRelatedObject1APIName> THEN < PossibleRelatedObject1FieldsListToQuery> WHEN <PossibleRelatedObject2APIName> THEN < PossibleRelatedObject2FieldsListToQuery>.... ELSE <LisfOfFieldsToQueryWhenAllAboveWHENConditionsAreFalse> END

It's similar to switch case in apex. For example, we can understand our TYPEOF clause as: TYPEOF Owner WHEN User Then FederationIdentifier END, here we checked if the TYPEOF Owner (primary sObject field) WHEN (is equal to) User (possible related object) THEN FederationIdentifier (field to query) END

Notice that the ELSE part is optional and we can have multiple WHEN-THEN combinations to check for multiple objects that are possible considering our polymorphic relationship field.

Another example can be WhatId field of Task object. We know that this field can be linked with multiple sObjects. Let's consider Account, Opportunity and Case for now and query different fields from each of these objects. If the task is not linked to any of the Account, Opportunity or Case object, I'll just query the name field of that related object's record.

Let's create 4 tasks: First one related to an account, second one related to an opportunity, third one related to a case and fourth one related to a product. Have a look at the tasks I created below:

Task linked to account

Task linked to opportunity

Task linked to case

Task linked to product

My apex code with the SOQL query is as follows:

List<Task> taskList = [SELECT Subject, 
    TYPEOF What 
    	WHEN Account THEN Type, NumberOfEmployees 
    	WHEN Opportunity THEN Amount, CloseDate 
    	WHEN Case THEN Subject, CaseNumber 
    	ELSE Name 
    END 
FROM Task];
for(Task taskRecord: taskList) {
    System.debug(taskRecord.Subject + ' -> ' + taskRecord.What);
}

As you can see above, I am querying Type and NumberOfEmployees when the related object is Account, Amount and CloseDate when the related object is Opportunity, Subject and CaseNumber when the related object is Case, otherwise, I'm just querying Name of the related record. The result when the above apex code is executed is shown below:

As you can see above, we're getting all the tasks with relevant related fields from each of Account, Opportunity and Case. In case of product, we're getting the Name field. 

If you remember, in our initial query on Case (SELECT Id, TYPEOF Owner WHEN User Then FederationIdentifier END FROM Case WHERE Id = '5005D000008nxIkQAI' AND Owner.Type = 'User'), we also added WHERE condition on Type field as: AND Owner.Type = 'User'. This is another good way to filter records while dealing with the specific type of related objects we would like to consider. You can even skip the ELSE part if you've already added WHEN-THEN for each related object and the corresponding conditions in WHERE clause.

So that's how you can Simplify SOQL with Polymorphic Relationships. You can have a look at this detailed article from salesforce official documentation to learn more about TYPEOF clause. Before coming to the end of this blog, let's talk about some of the important considerations of TYPEOF clause as well.

Considerations for TYPEOF

I am highlighting some of the considerations of TYPEOF clause here, you can checkout the full list in official documentation.

• TYPEOF cannot be used with with queries that don't return objects such as COUNT() and aggregate queries
• TYPEOF cannot be used in SOQL used in Bulk API
•  TYPEOF cannot be used in semi-join query. Semi Join query is a query used to filter the original query - For example: SELECT Name FROM Contact WHERE AccountId IN (SELECT Id FROM Account), here the text in bold is semi-join query
• TYPEOF cannot be used with a relationship field whose relationshipName or namePointing attribute is false.

You can checkout the namePointing and relationshipName attribute of a field very easily. For example, consider the below code:

System.debug(Case.OwnerId.getDescribe());

Here, we're getting the DescribeFieldResult for OwnerId field of Case object. The output for the same is provided below:

Schema.DescribeFieldResult[
    getByteLength=18;
    getCalculatedFormula=null;
    getCompoundFieldName=null;
    getController=null;
    getDataTranslationEnabled=null;
    getDefaultValue=null;
    getDefaultValueFormula=null;
    getDigits=0;
    getFilteredLookupInfo=null;
    getInlineHelpText=null;
    getLabel=Owner ID;
    getLength=18;
    getLocalName=OwnerId;
    getMask=null;
    getMaskType=null;
    getName=OwnerId;
    getPrecision=0;
    getReferenceTargetField=null;
    getRelationshipName=Owner; // <---- Relationship Name
    getRelationshipOrder=null;
    getScale=0;
    getSoapType=ID;
    getSobjectField=OwnerId;
    getType=REFERENCE;
    isAccessible=true;
    isAggregatable=true;
    isAiPredictionField=false;
    isAutoNumber=false;
    isCalculated=false;
    isCascadeDelete=false;
    isCaseSensitive=false;
    isCreateable=true;
    isCustom=false;
    isDefaultedOnCreate=true;
    isDependentPicklist=false;
    isDeprecatedAndHidden=false;
    isDisplayLocationInDecimal=false;
    isEncrypted=false;
    isExternalId=false;
    isFilterable=true;
    isFormulaTreatNullNumberAsZero=false;
    isGroupable=true;
    isHighScaleNumber=false;
    isHtmlFormatted=false;
    isIdLookup=false;
    isNameField=false;
    isNamePointing=true; // <---- Name Pointing
    isNillable=false;
    isPermissionable=false;
    isQueryByDistance=false;
    isRestrictedDelete=false;
    isSearchPrefilterable=false;
    isSortable=true;
    isUnique=false;
    isUpdateable=true;
    isWriteRequiresMasterRead=false;
]

If you notice, the relationshipName for this field is Owner and namePointing attribute is also true. A smaller documentation on TYPEOF clause is also available here.

That's all for this tutorial. I hope you liked it, let me know your feedback in the comments down below. You can connect with my on Connections app by scanning the QR code given below:

Or search in the code scanner screen using my username: rahulmalhotra

Happy Trailblazing!!