Apex Code Development You need to sign in to do that
Don't have an account?
chris.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.)
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.)
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:
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
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:
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)
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.
Hello, Chris,
The reason you can query for Owner.Latitude is because...
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):