+ Start a Discussion
chris.phoenix1.3911270665504087E12chris.phoenix1.3911270665504087E12 

Owner of Task: User vs. Name

In the schema (version 29) I can see that Task has a field OwnerId with relationshipName = Owner and referenceTo = User. And I see that User has a childRelationship to Task with field = OwnerId.

So then I do 

select owner.id from task limit 1

and I get

{:queryResponse=>{:result=>{:done=>true, :queryLocator=>nil, :records=>[{:type=>"Task", :Id=>nil, :Owner=>{:type=>"Name", :Id=>"005d0000000yQjxAAE"}}], :size=>"1"}}}

Note that the :type of the :Owner hash is Name, not User.

And indeed, if I try to get a field that's in User but not in Name, it fails...

Rforce query? > select owner.latitude from task limit 1
{:Fault=>{:faultcode=>"sf:INVALID_FIELD", :faultstring=>"INVALID_FIELD: \nselect owner.latitude from task limit 1\n       ^\nERROR at Row:1:Column:8\nNo such column 'latitude' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.", :detail=>{:InvalidFieldFault=>{:exceptionCode=>"INVALID_FIELD", :exceptionMessage=>"select owner.latitude from task limit 1\n       ^\nERROR at Row:1:Column:8\nNo such column 'latitude' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.", :row=>"1", :column=>"8"}}}}

So...
1) Why does the schema lie?
2) What's the workaround, to actually get the User who owns the Task?

Bonus question: Why is there a relationshipName (as promised in the documentation) in the Account->Opportunity :childRelationships, but there is none in the Account->User :childRelationships? Without a relationshipName, there seems to be no way to do a nested query in SOQL.

(This is all with the rforce gem in Ruby, but I really don't think it's the gem's fault.)

Best Answer chosen by chris.phoenix1.3911270665504087E12
Marty C.Marty C.

Hello, Chris, thank you for including all that detail in your post, and I'll try to answer all three of your questions.

First, the schema doesn't lie (at least not directly). The Task.OwnerId field is polymorphic[1], meaning that it can refer to more than one type of object. In the case of Tasks, the field can refer to either a User or a Calendar. Due to this complexity, Salesforce probably says the entity is "Name" just to avoid confusion with simple Lookup(User) fields. In the future you will likely be able to use a special TYPEOF clause in SOQL to handle polymorphic fields, but that is only available as a Developer Preview right now.

Second, the workaround is for you to execute two queries: one to get the list of Tasks for which you want owner information, and another to get the owner information. The steps involved are:

  1. Query for the Tasks you care about
  2. Loop through the results and compile a set of User ID's
  3. Query for data on the users matching the compiled set of ID's
  4. Run your logic on the Tasks you retrieved, using the User ID to look up the associated user information when needed

 

Lastly, I assume you meant there is no User->Account (not Account->User) relationship that you can use in your code. Salesforce probably set this limit knowing that every single object would introduce a new relationship, and use of these relationships may cause performance issues. Your workaround here will be similar to the one above, executing two queries and marrying the data together as needed.

[1]: polymorphic (http://www.salesforce.com/us/developer/docs/dbcom_apex/Content/langCon_apex_SOQL_polymorphic_relationships.htm)

All Answers

Marty C.Marty C.

Hello, Chris, thank you for including all that detail in your post, and I'll try to answer all three of your questions.

First, the schema doesn't lie (at least not directly). The Task.OwnerId field is polymorphic[1], meaning that it can refer to more than one type of object. In the case of Tasks, the field can refer to either a User or a Calendar. Due to this complexity, Salesforce probably says the entity is "Name" just to avoid confusion with simple Lookup(User) fields. In the future you will likely be able to use a special TYPEOF clause in SOQL to handle polymorphic fields, but that is only available as a Developer Preview right now.

Second, the workaround is for you to execute two queries: one to get the list of Tasks for which you want owner information, and another to get the owner information. The steps involved are:

  1. Query for the Tasks you care about
  2. Loop through the results and compile a set of User ID's
  3. Query for data on the users matching the compiled set of ID's
  4. Run your logic on the Tasks you retrieved, using the User ID to look up the associated user information when needed

 

Lastly, I assume you meant there is no User->Account (not Account->User) relationship that you can use in your code. Salesforce probably set this limit knowing that every single object would introduce a new relationship, and use of these relationships may cause performance issues. Your workaround here will be similar to the one above, executing two queries and marrying the data together as needed.

[1]: polymorphic (http://www.salesforce.com/us/developer/docs/dbcom_apex/Content/langCon_apex_SOQL_polymorphic_relationships.htm)

This was selected as the best answer
chris.phoenix1.3911270665504087E12chris.phoenix1.3911270665504087E12
OK, so I read the polymorphic page, and based on that I don't understand why the following didn't work:

Rforce query? > select owner.latitude from task where owner.type in ('user')

{:Fault=>{:faultcode=>"sf:INVALID_FIELD", :faultstring=>"INVALID_FIELD: \nselect owner.latitude from task where owner.type\n       ^\nERROR at Row:1:Column:8\nNo such column 'latitude' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.", :detail=>{:InvalidFieldFault=>{:exceptionCode=>"INVALID_FIELD", :exceptionMessage=>"select owner.latitude from task where owner.type\n       ^\nERROR at Row:1:Column:8\nNo such column 'latitude' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.", :row=>"1", :column=>"8"}}}}

If I do this:

select owner.id from task where owner.type in ('user')

then I get 19 objects whose :Owner -> :Id all start with 005, which is the keyPrefix for User.

If I leave off the "where owner.type in ('user')" then I still get just 19 objects. So every Owner of Task is a User. The fields are there but SalesForce is simply not giving them to me.


When you say "every single object would introduce a new relationship" I assume you mean "every single foreign-key field". There can be 0, 1, or multiple parent->child and child->parent relationships between any two objects. I note that there are many foreign-key fields which have a corresponding :childRelationship in the schema, but that relationship does not list a :relationshipName.


So it looks like I should just ignore the "parent and child accessor" page entirely! I can't get child records in subqueries (at least not for many of the parent->child relationships) and I can't get parental fields (at least for some parents, and with no indication in the schema that this is the case).

I guess I'll just use the workaround all the time, then. I'm skeptical that it's actually higher-performance to do two queries with a massive list of IDs filtering the second query, rather than just do a nested query.

Thanks for confirming that this is a real thing I'm seeing.
Marty C.Marty C.

Hello, Chris,

The reason you can query for Owner.Latitude is because...

  • Latitude is a field on the User object
  • OwnerId is a Lookup(Name) field, which allows it to be polymorphic
  • The Name object does not have Latitude as a field. See the screenshot below, taken from Workbench.

Name SObject fields

I can't speak to whether or not the community as a whole runs better under Salesforce's governors, limits and other restrictions, but they're not things we can change on a whim. In the case of what you're trying to do, please note that as generals rule (and all rules have exceptions):

  • All custom relationships you create should be usable in subqueries and also in WHERE __ IN (SELECT __ FROM __) clauses
  • Many standard relationships will now allow you to manipulate them in the same manner. Sadly, Task.OwnerId is one of them.
     
Marty C.Marty C.
I just noticed a typo. I meant to start my last reply with, "The reason you cannot query for Owner.Latitude..."