SOQL Query to extract certain attachments fails

Question

I want to export attachments of those Owners who belong to a certain Organisation.

I tried:

SELECT BodyLength FROM Attachment WHERE OwnerId.Organisation_Name__r = 'ExampleCompanyName', but got the following error:

ERROR at Row:1:Column:41 Didn't understand the relationship 'OwnerId' in the field path. 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 somebody help me out?

Answer 1

Assuming there is a field on User called Organisation_name__c

You have:

SELECT BodyLength 
  FROM Attachment 
  WHERE OwnerId.Organisation_Name__r = 'ExampleCompanyName'

The relationshipname from Attachment.OwnerId is called Owner, not OwnerId

so, you need:

SELECT BodyLength 
   FROM Attachment 
   WHERE Owner.Organisation_Name__c = 'ExampleCompanyName'

The TYPEOF (polymorphic) syntax is only relevant when the sobject can be owned by either users or queues - but Attachment can never be owned by a queue

Answer 2

Please try this

SELECT BodyLength,
  TYPEOF Owner
    WHEN User THEN Organisation_Name__c
  END
FROM Attachment
WHERE OwnerId IN
    (
    SELECT Id
    FROM User WHERE Organisation_Name__c = 'ExampleCompanyName'
    )

Source: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_and_polymorph_keys.htm