Search code examples
salesforcesoql

Who to access field of nested relationship in soql where first relation is simple and next is polymorphic


I want to access custom field of Owner (User) fields where Owner is polymorphic relation it can be either User or Group. I tried following query:

SELECT Id, Email, Name, Lead.TYPEOF Owner WHEN User THEN custom__c FROM CampaignMember WHERE CampaignId='xxxxxx'

Relationship stack is: Lead > Owner (User,Group) > custom__c.


Solution

  • SELECT      field1__c, 
                field2__c 
    FROM        Lead 
    WHERE       Owner.type = 'User' 
      AND       CampaignID = 'xxxxxx'
    

    EDIT:

    Leaving my original answer in case it helps someone else.

    I believe what you are looking for is this:

    SELECT Id, Email, Name,
        TYPEOF Lead.Owner
            WHEN User THEN custom__c
        END
    FROM CampaignMember
    WHERE Field != 'value'