SOQL to query parent fields from Polymorphic lookup relationship | TYPEOF clause in SOQL

Hello Trailblazers! Learning SOQL Queries is one of the first of the baby steps we took when started learning Salesforce. But there are some things that we overlooked, one of them is the TYPEOF clause in SOQL. Let us learn about that today!

Background

As you might know, there is one magical lookup field on Events and Tasks that can be assigned to any SObject in your org, WhatId. It is called the Polymorphic lookup field. The same type of field is used for the Owner lookup of any record in Salesforce, where the Owner either can be a Group, a Queue, or a User.

As the records assigned in the Polymorphic Lookups can be of different SObject types, they have different sets of fields, and there is a problem when we need to query these records using relationship queries.

Let's take an example, you want to query Event records that are related to Opportunity and Account. You can do that by specifying the What.Type in the where clause like below.

SELECT Id
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')

The Problem

Now in the above example, what if we need to query some fields from Opportunity and Account. Let's say Phone from Account and CloseDate from Opportunity.

We can't do the query like below.

SELECT Id, What.CloseDate, What.Phone
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')

It will not compile. Let's see the solution in the next section.

Solution

And here is we need to use the TYPEOF clause in SOQL.

SELECT 
  TYPEOF What
    WHEN Account THEN Phone
    WHEN Opportunity THEN CloseDate
    ELSE Name
  END
FROM Event

In the above query if the type of What is Account, then we are querying Phone, if its an Opportunity then CloseDate and Name for others.

Now let's see how we can use this in Apex Code.

Sample Apex Code

Task[] tasks = [
    SELECT
      TYPEOF What
        WHEN Account THEN Phone
        WHEN Opportunity THEN CloseDate
        ELSE Name
      END
    FROM Task
];
for(Task taskObj: tasks){
    if(taskObj.WhatId != null){
        String objType = taskObj.WhatId?.getSobjectType().getDescribe().getName();
        if(objType == 'Opportunity'){
            System.debug('CloseDate ' + taskObj.What.get('CloseDate'));
        } else if (objType == 'Account'){
            System.debug('Phone ' + taskObj.What.get('Phone'));
        } else {
            System.debug('Name ' + taskObj.What.get('Name'));
        }
    }
}

Note: What.Type and TYPEOF can't be used for the Same field in one SOQL.

References

Understanding Relationship Fields and Polymorphic Fields