1

I am trying to get all Accounts that has attachmentss. These accounts are of a particular record type, and has a certain value in Manufacture__c field

This is what i tried

list<Account> acc = [select Id, Name, CreatedDate,(SELECT Id, Name, ParentId, Parent.Type, CreatedDate FROM Attachment where Parent.Type = 'Account' And ParentId!=Null) 
                FROM Account Where Manufacturer__c = 'GENEX' And RecordtypeID = '012E0000000RTEdIAO'];
system.debug(' ************ ' +acc.size());

Got this error

Parent.Type, CreatedDate FROM Attachment where Parent.Type = 'Account' ^ ERROR at Row:1:Column:88 Didn't understand relationship 'Attachment' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

Can someone help me understand what's wrong with my query ?

3
  • 1
    Subqueries need to use the child relationship name. Can't provide a full answer right now.
    – Derek F
    Jan 13, 2020 at 20:52
  • 1
    Actually i found my mistake. Its Attachments not Attachment Jan 13, 2020 at 20:55
  • 1
    Right, "Attachments" (the plural) is the child relationship name in this case.
    – Derek F
    Jan 13, 2020 at 21:03

1 Answer 1

3

When you're putting a subquery in your SELECT clause (i.e. you're querying to see which child records are related to the main target of your outer query), you need to use the child relationship name rather than the normal label for the child SObject.

In your case, that means you need to use Attachments (the plural of the label) rather than Attachment.

From Understanding Relationship Names

For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other objects, and has a relationshipName for each, Assets, Cases, and Contacts.These relationships can be traversed only in the SELECT clause, using a nested SOQL query. For example:

SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account

This query returns all accounts, and for each account, the first and last name of each contact associated with (the child of) that account.

The Account.Contacts bit isn't strictly necessary. Just Contacts would do.

The "plural of the child object name" rule works for most standard relationship fields and some custom relationship fields (with an "__r" tacked on to the end), but not for everything.

The standard self-relationship on Account (ParentId), for example, has a child relationship name of ChildAccounts.

There are a couple of different ways to figure out what the child relationship name is, but probably the quickest/easiest is to run some anonymous apex to get that information from the SObject Describe info.

// We get at the child relationships from the "Parent" object
Schema.DescribeSObjectResult dso = Account.SObjectType.getDescribe();
for(Schema.ChildRelationship childRel :dso.getChildRelationships()){
    // getChildSObject() returns an SObject token, and we can directly compare it to
    //   <sobject Name>.SObjectType
    // Limiting to a single object (or you could store SObject tokens in a set and do 
    //   .contains(childRel.getChildSObject()) ) just helps cut down on clutter.
    // There can be more than one child relationship name to a given object (but the child
    //   relationship names will be different)
    if(childRel.getChildSObject() ==  Attachment.SObjectType){
        system.debug('Child relationship name = ' + childRel.getRelationshipName());
    }
}

Bonus help

You can do away with your WHERE clause in your subquery.

Since you are in a subquery where Account is the main object, you will only ever get results that are related to one of the records from your outer query.

The attachment's ParentId will never be null. If it were, then it wouldn't show up in the results of your subquery (which only returns child records that are related to one of the parent records your outer query returns).

Furthermore, the records your subquery returns are guaranteed to always be related to the main object, so Parent.Type will always be Account (for this particular query).

The overall query itself implicitly adding those restrictions, much like how if you query SELECT Parent.Name FROM Account, you don't need to check that Parent is actually an Account. The definition of the field itself prevents anything other than an Id for Account to be placed into that field.

You must log in to answer this question.

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