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.

EDIT: My answer for now is to query the NotesAndAttachments property of each of the containing object types (Account, Contact, Opportunity) and also pull Attachments from Case and project them as NotesAndAttachments to get the information I need. This process requires four roundtrips, and takes an average of about 15 seconds for 5 records' summary information (no note bodies or file binaries). I was able to push the data retrieval off to an asynchronous thread, which mitigates the retrieval time, but the performance will probably still be unsatisfactory. I'll continue to work on something more performant, but for now I've got it working.

Answer 1

There appears to be two issues here...

1. You cannot query the NotesAndAttachments object directly - this is a combination of the Notes and the Attachments object.
2. You can only query down to 1 level on parent-to-child relationships. You can have a sub-query to Contacts, but you cannot have a sub-query to notes or attachments on contacts.

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}'