Sub-Querying Objects, Plural Relationship Name?

Question

This may be pretty basic I've come across something in SOQL I'm not sure I totally understand.

List<Contact> con = [SELECT Id, (SELECT Id, Name, CreatedDate, ContentType FROM Attachments WHERE ContentType ='application/pdf' ORDER BY CreatedDate DESC) FROM Contact LIMIT 10];

Here I'm selecting a list of Contact records and Attachment records relevant to said Contact. The problem I have is querying Attachments given the object is called Attachment.

I know, for example, if this List<> was in a for loop, I can do things like:

for (Attachment a : con.Attachments) {
  if (a.Name.startsWithIgnoreCase('Resume_')) {
    [...]
  }
}

But again, I'm unsure as to why the object is called Attachment yet I'm allowed to query Attachments? Is it only in sub queries that you should use the plural of the object? Where can you find this information with respect to custom objects or is this pluralisation only for standard objects?

Answer 1

The relationship/lookup type fields are two-way streets. Defined on the child object, they typically have a name from child to parent (e.g. Attachment.ParentId), and from parent to child (e.g. Attachments). Most standard relationships will follow the naming convention of pluralizing the child object name as the relationship name when querying from the parent.

There are a few standard lookup fields that have no relationship name (and thus cannot be queried as a sub-query), and a precious few actually don't use the pluralized form of the child object's name as the relationship name. I don't remember any examples off the top of my head, but I know they exist, as I've run across them before.

It's important to note that you're not querying against all versions of the that object, but only one specific field in the child object. There aren't many examples of this distinction in the standard API, since most child objects only have one lookup field to a particular type of parent, but you can observe this effect with custom relationships.

For example, let's say you create two custom lookup fields on Contact, one called "Mother" and one called "Father."

You'd now have two custom relationships. In order to get all of the contact records that call a specific parent record Mother or Father, you'd have to query both relationships to get all of the children, which would look like this:

Contact[] people = [SELECT (SELECT Name FROM MothersChildren__r), 
                           (SELECT Name FROM FathersChildren__r)
                    FROM Contact];

This example should hopefully make it more obvious: we're getting two lists of contacts for each parent contact record, one where the Mother field points to that contact record, and one where the Father field points to that contact record.

Note that, because these are custom relationships, we can call them whatever we want (I simply chose MothersChildren and FathersChildren for illustrative purposes), and being custom, they always end in "__r", just has how custom field and object names end in "__c".

Answer 2

As always with child relationships, you can look at the ChildRelationship and see its relationshipName. In this case:

for (ChildRelationship relation : SObjectType.Contact.getChildRelationships())
    if (relation.getChildSObject() == Attachment.sObjectType)
        system.debug(relation.getRelationshipName());

Considering the child records are the "many" in a "many-to-one" relationship, it makes sense for the name to be plural.

Also worth peeking at A Deeper look at SOQL and Relationship Queries on Force.com and note that the child relationship name they use in their example of a Left Outer Join is also plural:

SOQL Query:

SELECT Name, (SELECT Name FROM Job_Applications__r) FROM Position__c

Result:

Answer 3

Attachments is the standard child relationship name for that object

Same with account.contacts, account.opportunities for example

If you create a lookup you have the ability to set the child relationship name. So if I look up to account I may name (not in real life) the child relationship "relatedAccountChildren" and would reference it from a parent record at relatedAccountChildren__r. This would be analogous to Attachments.

All of the above would be used in subqueries as they return a list of child records. You cannot access the like a lookup on the parent to the child.

I

Answer 4

if anyone still runs across this question, there is a great explanation from Salesforce docs that specifies how and when to use the plural version in queries.

Understanding Relationship Names

It is a very clear article and should answer all of ops questions. Happy coding!