Apex Code Development You need to sign in to do that
Don't have an account?
sravan36 Case Attachments within SOQL Joined query.
Hi All,
I am trying to retrieve Case attachments along with case records as part of my client requirement. But i am getting an error like Binary fields cannot be selected in join queries. Is there any way to get Case and related attachments within a single query . Below is my query
[SELECT Status,casenumber,(SELECT id,name,Body FROM Attachments) FROM Case]
Note: "Body" is must in attachments.
Thanks in Advance!!
Sravan.
SELECT id,name,Body, Parent.Status, Parent.caseNumber FROM Attachments where ParentId in:setCaseId
Hey Sravan, don't think this would be possible in a single query.
The body field's data is stored in base64 encoding and cannot be called in join queries - afraid you will have to go with two queries.
You cannot select blob fields/binary fields in parent to child query. Instead you can write two queries to fetch case in one and related attachments in another.
If this post is helpful please throw Kudos.If this post solves your problem kindly mark it as solution.
Thanks
Hello Sravan,
You can't receive the Body field for multiple records in a single query() call. If your query returns the Body field, your client application must ensure that only one row with one Attachment is returned; otherwise, an error occurs. A more effective approach is to return IDs (but not Attachment records in the Body field) from a query() call and then pass them into retrieve() calls that return the Body field.
You can query notes and attachments to get information about them, but you cannot filter on the body of the note or attachment. You cannot filter against the content of textarea fields, blobs, or Scontrol components in any object. For example, the following query is valid, and returns all account names and the owner ID for any notes associated with the account:
SELECT Account.Name, (SELECT Note.OwnerId FROM Account.Notes) FROM Account
However, the following query is not valid, because it attempts to evaluate information stored in the body of the note:
SELECT Account.Name, (SELECT Note.Body FROM Account.Notes WHERE Note.Body LIKE 'D%') FROM Account
If you remove the WHERE clause, the query is valid and returns the contents of the body of the note:
SELECT Account.Name, (SELECT Note.Body FROM Account.Notes) FROM Account
Hi All,
Thanks for your response!!
I tried below query with out any where condition but its not working. Is there any way to achieve this for Attachments.
My goal here is need to query case records along with their attachments in to a Case list, then i need to convert that list in to JSON String.
SELECT casenumber, (SELECT id,Body FROM Attachments) FROM Case
Thanks again!!!
Hello,
Yes this query will not work because as per design we cannot select blob fields/binary fields (Body field is blob) in parent to child query. I would suggest you to write two separate queries, one for Attachment Body and one for list of attachments for a case. You can find common results with the attachment ID common in both the queries.
SELECT id,Body FROM Attachments
SELECT casenumber, (SELECT id FROM Attachments) FROM Case
I think rather that fetching the Blob field in query, You just fetch IDs of Attachments associated with cases.
Ex. SELECT id,(SELECT id from Attachment) FROM Case
You will get the ids of attachment, so you can use these IDs for anything.
Let's Say I want to use the blob of attachment as email attachment
List<Messaging.Emailfileattachment> fileAttachments = new List<Messaging.Emailfileattachment>();
for (Attachment attachItr : [SELECT id,Name,Body FROM Attachment WHERE id IN: selectedAttachment])
{
Messaging.Emailfileattachment efa = new Messaging.Emailfileattachment();
efa.setFileName(attachItr.Name);
efa.setBody(attachItr.Body);
fileAttachments.add(efa);
}
the selectedAttachment will be the set of Attachment IDs associated with Cases.
Happy Coding!!
Thanks,
Amit
I have a situation where i'm using the email-to-case. The scenerio is :
1. If i use email-to -case then i'm not able to fetch the attachments from Case object
query --> SELECT CaseNumber, (Select Name from Attachments) from Case
This doesn't return any attachment.
2. If i use custom apex class :- inboundemailhandler class, I'm receiving the attachments from email and also able to fetch the attachments using the same equery..
Code --> global class GetEmailToSalesForce implements Messaging.InboundEmailHandler {
global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.Inboundenvelope envelope) {
Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
Case newCase = new Case();
newCase.SuppliedName = email.plainTextBody;
newCase.SuppliedEmail = envelope.fromAddress;
newCase.Subject = email.subject;
newCase.Status = 'New';
newCase.Origin = 'Email';
try{
insert newCase;
System.debug('New Case is created:'+newCase.Id);
}catch(Exception e){
system.debug('Got an exception:'+e);
}
if (email.binaryAttachments != null && email.binaryAttachments.size() > 0) {
for (integer i = 0 ; i < email.binaryAttachments.size() ; i++) {
Attachment attachment = new Attachment();
// attach to the newly created contact record
//attachment.ParentId = contact.Id;
attachment.ParentId = newCase.Id;
attachment.Name = email.binaryAttachments[i].filename;
attachment.Body = email.binaryAttachments[i].body;
insert attachment;
}
}
return result;
}
}
Can someone please let me know the reason.
Thanks.
Order by lastmodifieddate desc limit 1000
Replace Parent type with your object!!