Apex Code Development You need to sign in to do that
Don't have an account?
mrh SOQL query to retrieve attachments for cases
Hello
I am having some difficulties generating a SOQL query to return details from the Case and Attachment objects in one hit - hopefully someone can advise whether this is possible and if so to do it!
I have a custom apex controller which is successfully making an API call to the database to retrieve details from the Case object which retrieves me a list of Case details.
What I would like to do now is get the same query to return the same Case details, but also to return me details from the Attachment object that relate to these cases.
To further explain, the essential fields that I need for the query to return me are 'Case.CaseNumber, Attachment.Id where Attachment.ParentId = Case.Id'.
It looks as though joins are something that SOQL doesn't like, but can anyone help me with this?
Many thanks.
Mark
FYI. Managed to get the following solution to extract the information I required:
-------------------------------------------------------------------------------------------------------------------
SObject[] queryParentObject = Database.query('SELECT Id, CaseNumber,Status, (SELECT Id, Name FROM Attachments) FROM Case');
for (SObject parentRecord : queryParentObject){
Object ParentFieldValue1 = parentRecord.get('Id');
Object ParentFieldValue2 = parentRecord.get('CaseNumber');
Object ParentFieldValue3 = parentRecord.get('Status');
// Prevent a null relationship from being accessed
SObject[] childRecordsFromParent = parentRecord.getSObjects('Attachments');
if (childRecordsFromParent != null) {
for (SObject childRecord : childRecordsFromParent){
Object ChildFieldValue1 = childRecord.get('Id');
Object ChildFieldValue2 = childRecord.get('Name');
System.debug('Case Id: ' + ParentFieldValue1 + ' Case Number: ' + ParentFieldValue2 + ' Case Status: ' + ParentFieldValue3 + ' Att Id: '+ ChildFieldValue1 + ' Att FileName: ' + ChildFieldValue2);
}
}
}
All Answers
You'll need a sub-query, which is generally in the form as follows:
Subqueries are accessed as a list from the main query by the relationship name, so you would access them like this:
for(case c:results) { for(attachment a:c.attachments) { // Do something here } }The list should be present with a size of 0 if no results exist for that case (and isEmpty() should return true).
The "parent.id = case.id" part happens automatically, as you might have deduced from the above examples.
SOQL doesn't do JOINS, it does relationships. You can read all about it in the Web Services API Developer's Guide.
Many thanks for your help with this.
I have the retrieve working correctly now although I am now having a problem accessing the Attachment values again to use later when forming links to the cases.
I am storing the results of the query in a List<Case> object, lets call that object 'Bob'. I can reference the Case specific attributes within 'Bob' ok, but cannot work out how to access the Case.Attachment attributes. I need to get to the Attachment.Id value.
e.g: I can't get the correct reference below:
<apex:outputLink rendered="{!Bob.Status != statusRejected}" target="_blank" title="Download as PDF" value="/servlet/servlet.FileDownload?file={!Bob.Attachments.Id}"><apex:image value="{!$Resource.confirm_icon}"/></apex:outputLink>
What am I missing?
Thanks again for your help.
Mark
FYI. Managed to get the following solution to extract the information I required:
-------------------------------------------------------------------------------------------------------------------
SObject[] queryParentObject = Database.query('SELECT Id, CaseNumber,Status, (SELECT Id, Name FROM Attachments) FROM Case');
for (SObject parentRecord : queryParentObject){
Object ParentFieldValue1 = parentRecord.get('Id');
Object ParentFieldValue2 = parentRecord.get('CaseNumber');
Object ParentFieldValue3 = parentRecord.get('Status');
// Prevent a null relationship from being accessed
SObject[] childRecordsFromParent = parentRecord.getSObjects('Attachments');
if (childRecordsFromParent != null) {
for (SObject childRecord : childRecordsFromParent){
Object ChildFieldValue1 = childRecord.get('Id');
Object ChildFieldValue2 = childRecord.get('Name');
System.debug('Case Id: ' + ParentFieldValue1 + ' Case Number: ' + ParentFieldValue2 + ' Case Status: ' + ParentFieldValue3 + ' Att Id: '+ ChildFieldValue1 + ' Att FileName: ' + ChildFieldValue2);
}
}
}
Worked for me.. !