Simon Fell > Its just code > SOQL-R revisited

Sunday, January 21, 2007

It appears that a common misconception is that when you're navigating FK relationships, you use the target object name, this might be because of a lot of samples use account / contact, which happen to have relationships named account & contacts. But this is not the case, this won't work when there are multiple relationships to the same target object (createdBy, lastModifiedBy, anyone?). Its always the name of the relationship, here's a few more examples to demonstrate.

select name, createdBy.alias, lastModifiedBy.alias from Account

select name, from contact

select name, from Account

select id, role,, from accountPartner

One more thing you'll probably run into soon, is how polymorphic foreign key's are handled, in this case, the SOQL relationship actually points to a special SObject called Name, and you query fields on the Name object. Name contains a common subset of fields, if you need to access type specific fields, then you can use the Id and Type fields on Name to drive a retrieve call to fetch the type specific fields. You can tell if a relationship is one of these special relationships by looking at the namePointing flag on the field in the DescribeSObjectResult, or by looking at the type of the field in the enterprise WSDL. here's a Name example.

select id, subject,,, what.type, owner.alias from Task

Finally, Name (and couple of its friends) are only usable as the target of a relationship in SOQL-R, they are not directly queryable, e.g. select firstName, lastName, id from Name will return an error, this is what the queryable fiag in the describe results means.