You need to sign in to do that
Don't have an account?
Westec Intelligent Surv.
Query for all Notes And Attachments directly or indirectly related to an Account
I need a SOQL query that will retrieve a result set similar to what you get in the Notes And Attachments section of an Account. This list includes not only all Notes And Attachments with the Account itself as the parent, but also for all Cases, Opportunities and/or Contacts related to the Account. With the NoteAndAttachment entity not queryable directly, and with no UNION clause available, I don't even know where to begin writing a single all-includive query.
That means you will just have to do a few separate queries to build a list of IDs.
List<id> ids = new List<id>();
for(Account curr:[SELECT ID FROM Account where id =: accountID])
ids.add(curr.id);
for (Opportunies curr: [SELECT ID FROM Opportunities WHERE account =: accountID])
ids.add(curr.id);
...
List<Attachments> attachments = [SELECT name FROM Attachments WHERE ParentID IN: ids];
Does pretty much the same thing, just requires extra code.
All Answers
[SELECT name FROM Attachments WHERE ParentID =: accountid OR ParentID IN (SELECT ID FROM Case WHERE account =: accountID) OR ParentID IN (SELECT ID FROM Opportunities WHERE account =: accountid) OR ParentID IN (SELECT ID FROM Contacts WHERE account =: accountID)]
This should do the trick. I wrote it off the top of my head so there could be mistakes, but basically this uses Saleforce's pseudo joins. If you need a reference search developerforce.com for 'SOQL joins'. You should find sufficient references here to correct any mistakes if I made any in my code.
I'm actually using the API, and it says that only two semi-join sub-selects are allowed per query. I don't know if this is also a limitation in Apex but it doesn't work for me. You also can't use a semi-join with the OR operator.
There is no way to perform a single SOQL query to get the result set that you've described (both Notes and Attachments for the Account and all children records). You'll have to issue multiple SOQL queries. Is there a particular reason for not being able to do that?
That means you will just have to do a few separate queries to build a list of IDs.
List<id> ids = new List<id>();
for(Account curr:[SELECT ID FROM Account where id =: accountID])
ids.add(curr.id);
for (Opportunies curr: [SELECT ID FROM Opportunities WHERE account =: accountID])
ids.add(curr.id);
...
List<Attachments> attachments = [SELECT name FROM Attachments WHERE ParentID IN: ids];
Does pretty much the same thing, just requires extra code.
You could get all the child records for the Account in 1 SOQL query as such:
SELECT ID, (select if from Opportunities), (Select id from Contacts), (Select id from Cases) FROM Account where id =: accountID
However you'd still need to perform seperate queries on the Attachment and Note objects with the set of Ids gathered from the above query.
I had a related need and thought that I would share. In my case, I needed all attachments that were related to any account of a particular type. I wanted the resulting ID for both the attachment (Id) as well as the account (ParentID) from the Attachment object.
This seemed to do the job:
Select Id, ParentId FROM Attachment WHERE ParentId IN (SELECT Id FROM Account WHERE Type = 'Customer - Direct').
Attachments are not queryable..what to do? :( when i write query for attachments it returns nothing.
how to make it queryable??
@ibtesam
I'd say 1 of 2 things are true which is causing it to return you nothing.
1) The querying user does not have access to the Attachments
2) There are no attachments to query for.