Salesforce SOQL Query for “Notes And Attachments” of a site

Question

Basically, I need a single SOQL query, executable using the web service API, that will return all NoteAndAttachment items that would normally show in the Notes And Attachments section of an Account page in the Salesforce Web UI. This includes not only N&A that are attached to the Account itself, but also those that are attached to any Case, Opportunity, or Contact that is related to the Account. Salesforce itself seems to have no problem getting that information, but between SOQL limitations and restrictions on the data model, I have yet to find a satisfactory solution.

Here's what I have so far:

SELECT Id,

(select Id, Title, IsNote from NotesAndAttachments),

(select Id, Title, IsNote from Contacts.NotesAndAttachments),

(select Id, Title, IsNote from Cases.NotesAndAttachments),

(select Id, Title, IsNote from Opportunities.NotesAndAttachments)

FROM Account a WHERE Id = '{0}'

The {0} is for use with a .NET String.Format; it'll be the account ID I'm pulling records for. This particular query fails with the following exception:

System.Web.Services.Protocols.SoapException : INVALID_FIELD: 

(select Id, Title, IsNote from Contacts.NotesAndAttachments),

                               ^

ERROR at Row:3:Column:48

Didn't understand relationship 'Contacts' in 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.

These are all basic, system-defined relationships, and it didn't recognize the singular "Contact" either, so I'm at a loss.

Other solutions have involved semi-join sub-selects (there's a limit of two and you can't combine them with "OR", so no dice), nested subqueries (can't), etc. I'm really at my wits end with this query language's limitations; no UNION, limited subqueries, limited subquery depth, and the NoteAndAttachment entity cannot be queried directly. There simply has to be a way to get these records the same way they're shown in the website, but I'm being frustrated at every turn.

Answer 1

I think your only option here would be to do four queries. However this could cost you a lot of API calls if you are looping over multiple Account Ids.

SELECT Id,

(SELECT Id, Title FROM Notes),

(SELECT Id, Name FROM Attachments)

FROM Account WHERE Id = '{0}'

SELECT Id,

(SELECT Id, Title FROM Notes),

(SELECT Id, Name FROM Attachments)

FROM Contact WHERE AccountId = '{0}'

SELECT Id,

(SELECT Id, Title FROM Notes),

(SELECT Id, Name FROM Attachments)

FROM Case WHERE AccountId = '{0}'

SELECT Id,

(SELECT Id, Title FROM Notes),

(SELECT Id, Name FROM Attachments)

FROM Opportunity WHERE AccountId = '{0}'

Be a certified Salesforce professional by going for Intellipaat’s Salesforce Certification!