function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rajiv B 3Rajiv B 3 

combine two queries on case

is it possible to combine below two queries as one, where I need to display Case emails and comments in a page pertaining to a case order by createddate desc​
  
List <EmailMessage> sortedEmails = new List<EmailMessage>();
   
    sortedEmails = [SELECT Id, FromAddress, ToAddress, BCCAddress, MessageDate, Subject, HasAttachment, Incoming, TextBody, CreatedBy.Name 
            from EmailMessage where ParentId =: currentCase.Id 
            order by MessageDate DESC ];

List<CaseComment> Casecomments = new List<CaseComment>();
    Casecomments = [SELECT Id, CommentBody, CreatedDate, ParentId FROM CaseComment where ParentId =: currentCase.Id
            order by CreatedDate DESC];

 
Prakash NawalePrakash Nawale
Hi Ravi,

There is not relationshp between CaseComment and EmailMessage so you can't combine in single query.

I will suggest user Create Wrapper class like 
  
public class WrapperEmailCaseComment{
public string recordId{get;set;
public List <EmailMessage> sortedEmails {get;set;}
public List<CaseComment> Casecomments {get;set;}
public WrapperEmailCaseComment(){
	sortedEmails = new  <EmailMessage>();
	Casecomments = new  List<CaseComment> ();
}

}


//Create map

Map<Id,WrapperEmailCaseComment> EmailCaseCommmentByParentId = new Map<Id,WrapperEmailCaseComment>();

// Do soql quries
    
for(EmailMessage  record :[SELECT Id, FromAddress, ToAddress, BCCAddress, MessageDate, Subject, HasAttachment, Incoming, TextBody, CreatedBy.Name  from EmailMessage where ParentId =: currentCase.Id  order by MessageDate DESC ]){
rapperEmailCaseComment  = null;
if(EmailCaseCommmentByParentId.containskey(record.ParentId)){

	 emailCaseComment = EmailCaseCommmentByParentId.get(record.parentId));
	  emailCaseComment.sortedEmails.add( record);
	 
​ } else{
		
		WemailCaseComment = new WrapperEmailCaseComment();
		emailCaseComment.parentId = record.parentId;
		emailCaseComment.sortedEmails.add( record);
		
			
	}
	EmailCaseCommmentByParentId.put(record.ParentId,emailCaseComment);
}
   
for(CaseComment record : [SELECT Id, CommentBody, CreatedDate, ParentId FROM CaseComment where ParentId =: currentCase.Id             order by CreatedDate DESC]){
rapperEmailCaseComment  = null;
if(EmailCaseCommmentByParentId.containskey(record.ParentId)){

	WrapperEmailCaseComment emailCaseComment = EmailCaseCommmentByParentId.get(record.parentId));
	emailCaseComment.Casecomments.add( record);
	 
​ } else{
		
		WrapperEmailCaseComment emailCaseComment = new WrapperEmailCaseComment();
		emailCaseComment.parentId = record.parentId;
		emailCaseComment.Casecomments.add( record);
			
	}
	EmailCaseCommmentByParentId.put(record.ParentId,emailCaseComment);
}

convert this EmailCaseCommmentByParentId map to list.

Please mark this as best if it helps to you.

Regards,
Prakash
nawaleprakash@gmail.com
Rajiv B 3Rajiv B 3
Hi Prakash, 
Many thanks. 

unbale to figure it out, not working for my requirment 

 I am struck how to use above code to display  realted emails and casecomments pertaiining to a case in descending order by createddate/emailmessage