Forcetrails: 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



5 comments:
  1. hi, thank you so much for this information. I've tried with my case and it worked perfectly. However I wanted to ask you if you know if it is possible to navigate to another object, also retrieving a value related to the object Opportunity. For example, in your example, using only the Opportunity:

    Task[] tasks = [
    SELECT
    TYPEOF What
    WHEN Opportunity THEN CloseDate, OwnerId.Username
    ELSE Name
    END
    FROM Task
    ];

    I was totally able to add this in a query, and it worked, the problem comes when I want to get this object:

    if(objType == 'Opportunity'){
    System.debug('Username' + taskObj.What.get('OwnerId.Username'));

    How should I get this object?

    Thank you again!

    ReplyDelete
    Replies
    1. And, maybe I should add in my case I am working with Service Appointment and WorkOrder which both are standard objects, but I want to navigate from WorkOrder to Exploitation__r.(customfield) which as you can already tell is a custom object. Maybe this is why the What.get() is not working? thank you!

      Delete
    2. I will add my query here in case you need it for more information.
      [SELECT Id,
      TYPEOF ParentRecord
      WHEN WorkOrder THEN Id, Origin__c, Exploitation__c, Exploitation__r.SIT_Id__c, WorkOrderNumber
      END
      FROM ServiceAppointment WHERE Status =: Constants.SA_STATUS_DISPATCHED AND Id IN : saById.keySet()]

      As you can already tell, Exploitation__r.SIT_Id__c is the custom object field I am navigating towards.

      thank you again!

      Delete
    3. Hi Mo, I am glad that this post has helped you. I think you can get that data Exploitation__c expObj = (Exploitation__c) What.get('Exploitation__r');
      then access it like expobj.SIT_Id__c

      In the same way you can get the owner Id,
      User usrObj = (User) taskObj.What.get('Owner');
      access username using usrObj

      let me know if this helps you.

      Delete
    4. it did indeed! thank you!

      Delete

Hi there, comments on this site are moderated, you might need to wait until your comment is published. Spam and promotions will be deleted. Sorry for the inconvenience but we have moderated the comments for the safety of this website users. If you have any concern, or if you are not able to comment for some reason, reach email us at rahul@forcetrails.com