5

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.

1 Answer 1

7

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.

2
  • 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, 2015 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, 2015 at 0:38

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .