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

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 Phone and NumberOfEmployees fields. If the field references an Opportunity, the query will retrieve that Opportunity’s Amount and 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:

Account records:

Name Phone Number Of Employees
Acme (212) 555-5555 680
Global Media (905) 555-1212 14668

 

Opportunity records:

Name Amount Close Date
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

 

Event records:

Subject Name of Account or Opportunity referenced by What field
Acme Event Acme
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 “[email protected]”, 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!

tagged , , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • Chirag Mehta

    Awesome!

    I will wait fr that day when soql will no longer be abstraction of sql and will provide all features of sql (though I understand we are multi tenant etc) .. sounds like we are near ..

  • Anonymous

    Thats owsome feature release in winter 13….

  • Kapil Choudhary

    Great feature as well as Great post. Many thanks to dan yu.

  • Anonymous

    comment from mike, nice work

  • http://twitter.com/hemgarg hemant garg

    This is really cool, I have came across the situations where I had to write multiple queries specially while processing events/tasks.

  • http://www.facebook.com/kyunussov Kirill Yunussov

    Bad News:

    I just tried to add it to our org, and the salesforce support said that this feature is not currently available for Production or Sandbox. The only way to test it out is in a developer org. They said they don’t have an estimate on when it will be available in Prod.

  • http://www.facebook.com/kyunussov Kirill Yunussov

    Bad News:

    I just tried to add it to our org, and the salesforce support said that this feature is not currently available for Production or Sandbox. The only way to test it out is in a developer org. They said they don’t have an estimate on when it will be available in Prod.

  • http://twitter.com/FloFlorFlorcita florencia

    yes!! excellent