4

I actually can't believe I've not come across this before now, but is there a way of following on the owner relationship on a Task to get some other field on the User object?

For an example, say on the User object there is a custom field called MyField__c, and I want to query that while querying tasks:

select Id, Subject, Owner.MyField__c from Task

Seems easy, but the Owner field is one of those polymorphic lookups peculiar to tasks and activities, as tasks can also be owned by a calendar. If you create a formula field you can follow the relationship using this syntax:

Owner:User.MyField__c

which again, is something I've not seen until now but it's easy to see that :User is qualifying the relationship type. This syntax does not parse in SOQL, so the question is, is there an alternative syntax that does work?

Obviously I can just create the formula field and query by that, but I'm curious to know if there is a way of properly traversing this relationship in SOQL.

5

Yes, you need to use the TYPEOF operator. You can have multiple WHEN clauses in the SELECT, one for each valid type.

Example:

SELECT Id, Subject, 
TYPEOF Owner
 WHEN User THEN Owner.MyField__c
END
FROM Task

Additional information can be at SOQL Polymorphism.

  • Chris Bland showed me this shortly after posting. I don't want to move the goal posts but I actually wanted to filter by that field in the WHERE clause which can't be done with TYPEOF right now. – Matt Lacey Oct 14 '15 at 20:53
  • True. You'd either need a formula field to indicate type, a preliminary query, or to filter on the object's prefix. Is there anything on IdeaExchange to allow TYPEOF in inner queries? – Mike Chale Oct 15 '15 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.