Monday 7 September 2020

Polymorphic Relationships in SOQL Queries : TYPEOF

 Lets understand what is Polymorphic Relationships. In a polymorphic relationship, the referenced object of the relationship can be one of several different types of object for example who and what field on Task and Event Object. Who can be Contact or Lead. 

Relationship field and Polymorphic field

Let understand the different between Relationship field and Polymorphic field

Relationship field
Polymorphic field

 OwnerId field of the Account and contact object. Most of the Lookup and master-detail fields.    

OwnerId field of the event Object. It can be Calender or User. Or Who

 Example

Select id, Owner.Name from Account

Example

SELECT Id, Owner.Name FROM Event WHERE Owner.Type = 'User'

To get Parent record data use dot(.).

 You can use a TYPEOF clause in a query

 

What Field is also polymorphic field on Event/Task object. What about if we need to get field base on SObject Type? We have solution of this problem : TYPEOF.

TypeOF

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. TYPEOF is available in API version 46.0 and later (It is also available in API version 26.0 and later as part of a Developer Preview).

Syntax:

SELECT fieldList
        TYPEOF typeOfField
            WHEN whenObjectType THEN whenFieldList [...]
            ELSE elseFieldList
        END
FROM objectType

 

Use Case:

On Task Object "Related To" (What) is Polymorphic field. Which can be account, opportunity or other sobject. If "Related To" (What) field is related to account then we need to get account phone and number of employees fields. If the "Related To" (WhatId) field is related to Opportunity then we need to get amount and closeDate.

Set see how we can create our query for above requirement.

SELECT
      TYPEOF What
        WHEN Account THEN Phone, NumberOfEmployees
        WHEN Opportunity THEN Amount, CloseDate
        ELSE Name
      END
FROM Task
WHERE What.Type In ('Account','Opportunity')
  •  In this query we used TYPEOF to get runtime type of the polymorphic reference field.
  • We can use the TYPE to filter our query.


Let see how to handle it in Apex Class

List<Task> listTask = [SELECT
                                        TYPEOF What
                                           WHEN Account THEN Phone, NumberOfEmployees
                                           WHEN Opportunity THEN Amount, CloseDate
                                           ELSE Name
                                        END
                                   FROM Task
                                   WHERE What.Type In ('Account','Opportunity')];
    
for (Task tsk: listTask) {
    if (tsk.What instanceof Account) {
        Account acc = tsk.What;
        System.debug('--acc--->'+acc.NumberOfEmployees);
    } else if (tsk.What instanceof Opportunity) {
        Opportunity opp = tsk.What;
        System.debug('--opp--->'+opp.CloseDate);
    }
}
  1.  Use InstanceOf to check the Object Type.


2 comments:

  1. Very informative, Thank you.
    Can we create polymorphic fields and relations on custom objects?

    ReplyDelete
  2. Unfortunately we can't

    ReplyDelete