Understanding Relationship Fields and Polymorphic Fields

Some fields are relationship fields, which means they can be used to get information about a related object. And some of those relationship fields are polymorphic fields. A polymorphic field is one where the related object might be one of several different types of objects. For example, the Who relationship field of a Task can be a Contact or a Lead.

Example of a Relationship Field

SELECT Id, Owner.Name FROM Account

Example of a Polymorphic Field

This means it is a polymorphic field. Owner could be a Calendar, or a User. For example, you can use the following SOQL query:

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

Using Polymorphic Fields

Common Polymorphic Fields

Common polymorphic fields include:

• Owner: This field represents the parent of the record. For example:
  SELECT Id, Owner.Name
  FROM Task
  WHERE Owner.FirstName like 'B%'

  This example query works for Task records whose owners are either Calendars or Users.

• Who: This field represents the person associated with the record. For example:
  SELECT Id, Who.FirstName, Who.LastName
  FROM Task
  WHERE Owner.FirstName LIKE 'B%'

  This example query works for Task records whose owners can be either Calendars or Users, and whose Who field can be either Contacts or Leads.

  If you'd like to know the type of object returned in a query, use Who.Type. For example:

  SELECT Id, Who.Id, Who.Type
  FROM Task

  Using this example, you can query all the Tasks associated with Contacts:

  SELECT Id, Who.Id, Who.Type
  FROM Task
  WHERE Who.Type='Contact'

• What: This field represents nonhuman objects that are associated with the record. For example:
  SELECT Id, What.Name
  FROM Event

  This example query works for Events where What can be an Account or a Solution, or any of another number of object types.

Using the Type Qualifier

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

Using TYPEOF

SOQL supports polymorphic relationships using the TYPEOF expression in a SELECT statement. 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.)

SELECT 
  TYPEOF What
    WHEN Account THEN Phone, NumberOfEmployees
    WHEN Opportunity THEN Amount, CloseDate
    ELSE Name, Email
  END
FROM Event

Note the following considerations for TYPEOF.

• TYPEOF can’t be used with a relationship field whose namePointing attribute is false.
• TYPEOF can’t be used with a relationship field whose relationshipName attribute is false.
• TYPEOF is only allowed in the SELECT clause of a query. You can filter on the object type of a polymorphic relationship using the Type qualifier in a WHERE clause. For details, see Filter on Polymorphic Relationship Fields.
• TYPEOF isn’t allowed in queries that don’t return objects, such as COUNT() .
• TYPEOF can’t be used in SOQL queries that are the basis of Streaming API PushTopics.
• TYPEOF can’t be used in SOQL used in Bulk API.
• TYPEOF expressions can’t be nested. For example, you can’t use TYPEOF inside the WHEN clause of another TYPEOF expression.
• TYPEOF isn’t allowed in the SELECT clause of a semi-join query. You can use TYPEOF in the SELECT clause of an outer query that contains semi-join queries. The following example is not valid because TYPEOF is used in the semi-join query:
  SELECT Name FROM Account
  WHERE CreatedById IN
      (
      SELECT 
          TYPEOF Owner
              WHEN User THEN Id
              WHEN Group THEN CreatedById
          END
      FROM CASE
      )

  The following example is valid because TYPEOF is only used in the outer SELECT clause:
  SELECT 
      TYPEOF What
          WHEN Account THEN Phone
          ELSE Name
      END
  FROM Event
  WHERE CreatedById IN
      (
      SELECT CreatedById
      FROM Case
      )

• TYPEOF can’t be used in queries with functions in the SELECT clause. The following example is not valid because the TYPEOF includes the FORMAT function.
  SELECT
   TYPEOF What
    WHEN Account THEN Id, FORMAT(LastModifiedDate) LastModifiedDate__f
    WHEN Oppty THEN Id
   END
  FROM Task

  Instead, run the same query without functions to retrieve a list of IDs.
  SELECT
      TYPEOF What
          WHEN Account THEN Id, LastModifiedDate
          WHEN Opportunity THEN Id
      END
  FROM Task

  Then, run a second query with functions on the resulting ID list.
  SELECT 
      FORMAT(LastModifiedDate) LastModifiedDate__f 
  FROM Account 
  WHERE Id in RetrievedIdList

• TYPEOF can’t be used in queries with GROUP BY, GROUP BY ROLLUP, GROUP BY CUBE, and HAVING.

Combining TYPEOF and Type

SELECT Id,
  TYPEOF What
    WHEN Account THEN Phone
    WHEN Opportunity THEN Amount
  END
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')

Object Types in WSDLs

private com.sforce.soap.enterprise.sobject.SObject Owner;