Working with Polymorphic Relationships in SOQL Queries

Polymorphic Relationships

Polymorphic fields are defined as fields that can reference more than one Object. A polymorphic lookup field is a lookup field that allows you to select a record from more than one table therefore ‘morphing’ into many (poly!) tables. For example:

• A Lead or Case Owner can be either a User or a Queue record since the Lead Owner and Case Owner are both Lookup (User, Queue) fields.
• Who relationship field of a Task can be a Contact or a Lead.
• What: This field represents nonhuman objects that are associated with the record.

How to check if a field is Polymorphic?

To determine what kind a field is, call describeSObjects() on the object and examine the properties of the field. Also, you can check the below properties in Workbench.

• If relationshipName is not null, the field is a relationship field.
• If, in addition, namePointing is true, polymorphicForeignKey is true, and referenceTo has more than one referenced object type, then the field is polymorphic.

How to use Polymorphic fields in SOQL query?

You can use polymorphic fields in several ways.

• You can use the polymorphic field for the relationship.

            [SELECT Id, What.Name FROM Event]

• You can use the Type qualifier on a polymorphic field to get the associate object name. For example, if you wanted to pull events created only under Account and Opportunity, you can use as below:

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

• You can use a TYPEOF clause in a query. A TYPEOF expression specifies a set of fields to select that depends on the runtime type of the polymorphic reference. For example, you need to find the Event information along with reference object information. If the event is linked with Account pull Phone and  NumberOfEmployees, if the event is linked to Opportunity then get THEN Amount, CloseDate otherwise get email and name. 

            [SELECT  

                TYPEOF What

                    WHEN Account THEN Phone, NumberOfEmployees

                    WHEN Opportunity THEN Amount, CloseDate

                    ELSE Name, Emai

                END

            FROM Event]