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.


6 comments:

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

    ReplyDelete
  2. Unfortunately we can't

    ReplyDelete
  3. Wow its a great blog.
    Best Jewellery Wholesaler in Jaipur is Ratnavali. It is a best place to buy silver jewellery Jaipur, Jaipuri gold jewellery, gemstone figures in Jaipur, Rajasthan. We have skilled craftsmanship who made best designer collection of jewellery for you.

    ReplyDelete
  4. Thank you for sharing this valuable information.

    Apparrant is the leading UX UI Design agency having experienced designers and developers working throughout the year on important and life changing products. We have been awarded as top UI UX Design agency in India by multiple agencies. If you are looking for best UX Design Agency in Noida, UI UX Design Agency in Faridabad, and UX UI Design Company in Delhi then Apparrant is best for you.

    ReplyDelete