SOQL polymorphism is available as a developer preview feature in Winter ’13. Using SOQL polymorphism, you can now do in a single query what would have taken multiple queries, thereby reducing the amount of code you need, and making your code easier to understand and manage.
Polymorphic relationships are not as scary as they sound. A polymorphic relationship is a relationship where the referenced objects can be one of several different object types. For example, the
What relationship field in an Event can reference an Account, or a Campaign, or an Opportunity. The
LastModifiedBy relationship field in an Event, however, can only reference a User and is not polymorphic.
Without SOQL polymophism, you have to do extra work when making queries that involve polymorphic relationships. Since you don’t know in advance what type of object is being referenced, you might have to get the ID of a Name object instead, process this information in some logic code, and then do a second query using information from that Name object to get to the content of the referenced object. SOQL polymorphism lets you do all of this work in a single query.
SOQL polymorphism example using TYPEOF
With SOQL polymorphism you can provide instructions directly in the SOQL query on what to do for each possible type of object. You do this via the new
TYPEOF clause. Here’s an example using the
Event.What polymorphic relationship mentioned earlier:
SELECT Subject, TYPEOF What WHEN Account THEN Phone, NumberOfEmployees WHEN Opportunity THEN Amount, CloseDate END FROM Event
The contents of the
TYPEOF clause is where the SOQL polymorphism magic happens. For each
Event record, the
What field will be checked. If the field references an Account, the query will retrieve the referenced Account’s
NumberOfEmployees fields. If the field references an Opportunity, the query will retrieve that Opportunity’s
CloseDate fields. Note that we’re also selecting the
Subject field from Event, showing we can mix regular field selection with polymorphic field selection.
Suppose you had the following Account, Opportunity and Event records in your organization:
|Name||Phone||Number Of Employees|
|Global Media||(905) 555-1212||14668|
|Global Media – 400 Widgets||40000.0||2010-02-28|
|Acme – 1,200 Widgets||140000.0||2010-01-30|
|Acme – 600 Widgets||70000.0||2010-03-28|
|Acme – 200 Widgets||20000.0||2010-05-31|
|Subject||Name of Account or Opportunity referenced by What field|
|Global Media Event||Global Media|
|Global 400 Widget Event||Global Media – 400 Widgets|
|Acme 600 Widget Event||Acme – 600 Widgets|
Running the previously shown SOQL query would result in something like the following result set, where fields from both related Account and Opportunity records are retrieved:
|Event.Subject||Account.Phone or Opportunity.Amount||Account.NumberOfEmployees or Opportunity.CloseDate|
|Acme Event||(212) 555-5555||680|
|Global Media Event||(905) 555-1212||14668|
|Global 400 Widget Event||40000.0||2010-02-28|
|Acme 600 Widget Event||70000.0||2010-03-28|
Tell me more
For a working demo of SOQL polymorphism, take a look at the demo used for the “Using SOQL to Boost Mobile and Web App Performance” Dreamforce 2012 talk. Go to https://login.salesforce.com and log in as “firstname.lastname@example.org”, with a password of “123456”. Click on the “PolyDemo” tab to see a working example of a SOQL query using SOQL Polymorphism to query the What field of the Event object.
SOQL polymorphism is developer preview in Winter ’13, so please contact saleforce.com to enable this feature for your organization. Also, note that SOQL polymorphism
TYPEOF clauses cannot be used in SOQL used in the Streaming API or Bulk API. More details on using SOQL polymorphism and considerations are in the SOQL TYPEOF documentation in the SOQL and SOSL Reference Guide.
SOQL polymorphism is a powerful feature that can simplify your code. Play around with a few queries and you might learn to love them too!