Salesforce: Query Polymorphic Relationship Fields with a Single SOQL Query

In Salesforce, polymorphic relationship fields are versatile fields that can reference multiple object types. They simplify relationships between objects by allowing a single field to point to different types of records. For example, the Who field in a Task can reference a Contact or a Lead, and the What field can point to an Account, Opportunity, or another object.

This article explains how to query polymorphic relationship fields in Salesforce using SOQL. It covers the basics of polymorphic relationships, advanced querying techniques like filtering and TYPEOF, and practical examples to illustrate how to work with these fields effectively.

What Are Polymorphic Relationship Fields?

Polymorphic relationship fields allow a field to reference multiple object types, enabling dynamic relationships. Examples include:

1. Who Field: Used in Task and Event objects, referencing either a Contact or a Lead.
2. What Field: References objects such as Account, Opportunity, or custom objects.

Polymorphic relationships enhance flexibility by allowing a single field to store relationships to different objects. However, they require special techniques when queried using SOQL.

Querying Polymorphic Fields in SOQL

Polymorphic relationship fields can be queried using SOQL in two main ways:

1. Filtering by Object Type Using Type Qualifier
2. Using the TYPEOF Clause in SOQL

1. Filtering by Object Type Using Type Qualifier

The Type qualifier is used to filter results based on the referenced object type. For example, to retrieve Event records related to either Account or Opportunity objects via the What field:

List<Event> events = [SELECT Description FROM Event WHERE What. Type IN ('Account', 'Opportunity')];

Explanation:

• The What Type field identifies the type of object referenced.
• This query filters Event records to include only those where the What field points to an Account or an Opportunity.

2. Using the TYPEOF Clause in SOQL

The TYPEOF clause provides a more powerful way to query polymorphic fields by selectively retrieving fields based on the type of the referenced object.

For example, to query Events related to either Account or Opportunity:

List<Event> events = [
    SELECT TYPEOF What 
           WHEN Account THEN Phone 
           WHEN Opportunity THEN Amount 
           END 
    FROM Event
];

Explanation:

• The TYPEOF clause dynamically selects fields based on the type of the referenced object (What in this case).
• For Account, it retrieves the Phone field. For Opportunity, it retrieves the Amount field.

Accessing Referenced Objects in Polymorphic Relationships

After querying polymorphic fields, you may need to process the referenced objects. This is where the instance of keyword comes into play. It determines the actual type of the referenced object at runtime.

Example: Using instance of to process Event objects:

Event myEvent = eventFromQuery;

if (myEvent.What instanceof Account) {
    // `What` references an Account
    Account relatedAccount = (Account) myEvent.What;
    System.debug('Account Name: ' + relatedAccount.Name);
} else if (myEvent.What instanceof Opportunity) {
    // `What` references an Opportunity
    Opportunity relatedOpportunity = (Opportunity) myEvent.What;
    System.debug('Opportunity Amount: ' + relatedOpportunity.Amount);
}

Steps:

1. Use instance of to check the object type.
2. Cast the referenced object to the appropriate type (Account or Opportunity).
3. Process the object accordingly.

Practical Example: Querying and Processing Polymorphic Relationships

Scenario: Querying User or Group Owners of Custom Objects

Suppose you want to query Merchandise__c records owned by either User or Group objects. You can use the TYPEOF clause and instance of to handle the polymorphic Owner field.

public class PolymorphismExampleClass {

    // Utility method for processing User
    public static void processUser(User theUser) {
        System.debug('Processed User: ' + theUser.LastName);
    }
    
    // Utility method for processing Group
    public static void processGroup(Group theGroup) {
        System.debug('Processed Group: ' + theGroup.Name);
    }

    public static void processOwnersOfMerchandise() {
        // Query Merchandise__c records with the TYPEOF clause
        List<Merchandise__c> merchandiseList = [
            SELECT TYPEOF Owner 
                   WHEN User THEN LastName 
                   WHEN Group THEN Name 
                   END 
            FROM Merchandise__c
        ];
        
        // Process each record based on the Owner type
        for (Merchandise__c merch : merchandiseList) {
            if (merch.Owner instanceof User) {
                User userOwner = (User) merch.Owner;
                processUser(userOwner);
            } else if (merch.Owner instanceof Group) {
                Group groupOwner = (Group) merch.Owner;
                processGroup(groupOwner);
            }
        }
    }
}

Explanation:

1. The TYPEOF clause retrieves specific fields (LastName for User and Name for Group).
2. The instanceof keyword determines the actual type of the Owner field at runtime.
3. The utility methods processUser and processGroup handle the logic for processing each type.

Benefits of Querying Polymorphic Fields in SOQL

1. Dynamic Data Retrieval: The TYPEOF clause allows you to selectively retrieve fields based on object type.
2. Efficient Queries: By filtering with Type or using TYPEOF, you reduce the number of unnecessary fields returned.
3. Simplified Processing: The combination of TYPEOF and instance of simplifies handling polymorphic relationships programmatically.

Key Considerations

1. Governor Limits: Queries with TYPEOF and instance of are subject to standard SOQL governor limits. Optimize queries to avoid performance issues.
2. Field Accessibility: Ensure the queried fields are accessible to the running user’s profile or permission set.
3. Casting Types: Always cast the polymorphic reference to the appropriate type before using it.

Conclusion

Polymorphic relationships in Salesforce offer powerful flexibility for managing complex relationships between objects. By leveraging advanced SOQL techniques such as filtering with Type qualifiers and using the TYPEOF clause, developers can efficiently query and process polymorphic fields. Additionally, the instanceof keyword enables runtime type determination, simplifying logic for processing the referenced objects.

Understanding and applying these concepts allows developers to build robust, scalable Salesforce applications tailored to dynamic business needs.