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
Gaby FrydmanGaby Frydman 

SOQL through 3 objects in child-parent relationship

Hi All, 

I am trying to obtain all attachments from a Contact that has a record on  Application__c which belongs to an object called Job__c.

The Application__c is in many-to-many (lookup) with Contact and master-detail with Job__c

The idea is that If I have the Id of the Job__c, to loop over it's applicaitons and obtain the attachemnts from the contact 

I have currently the following code which does the job if i have the contact id :
  
Id jobId = 'a0K24000000QneY';

list<Attachment> attachments = 
    [SELECT Id, Name, Body, ContentType FROM Attachment 
     WHERE ParentId IN
		(SELECT Id from Contact WHERE id =: jobId)];

				
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {'secret@secret.com'};
email.setToAddresses(toAddresses);
email.setSubject('Foo');
email.setPlainTextBody('Bar');

List<Messaging.EmailFileAttachment> emailAttachments = new List<Messaging.EmailFileAttachment>();
for(Attachment att : attachments) {

    Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment();
    efa.setFileName(att.Name);
    efa.setBody(att.Body);
    efa.setContentType(att.ContentType);
    emailAttachments.add(efa);
}

email.setFileAttachments(emailAttachments);
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { email });

Since I cannot have more than one level query in child-parent relationship, what can do the job?
Nitin PaliwalNitin Paliwal
Before i answer your question,
Could you please help me understand few things
1 --- does application has a lookup of contact or contact has a lookup of application?
2 --  Is Job the master object or applicatione?
3 -- Do you have the set of id's for the Job object in  your code?

Thanks
Nitin
Gaby FrydmanGaby Frydman
Hi, 

1- Application has lookup to contact
2 - Job is master object od application
3- Yes, I do - I will be running it in the apex code console in the developer console - I will set the JobId's manually in the  
Id jobId = 'a0K24000000QneY';

The lookup from Application to contact is called Candidate_Contact__c 
Gaby FrydmanGaby Frydman
Id jobId = 'a0K24000000QneY';
List<Application__c> existingApplications = new List<Application__c>();
Job__c jobOrder;
List<Contact> existingContacts = new List<Contact>();
List<Id> contactIds;
list<Attachment> attachments;


if (jobOrder == null) {
existingApplications = [SELECT Id, Candidate_Contact__c FROM Application__c WHERE Job__c =: jobId];
	for (Application__c appl : existingApplications) {
    	contactIds.add(appl.Candidate_Contact__c);                
    	existingContacts =[SELECT id from Contact where id IN :contactIds];        
    
    for(Contact c: existingContacts){
        attachments = [SELECT Id, Name, Body, ContentType FROM Attachment WHere id in :contactIds ];
    
    }
  } 
}
				
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {'secret@secret.com'};
email.setToAddresses(toAddresses);
email.setSubject('Foo');
email.setPlainTextBody('Bar');

List<Messaging.EmailFileAttachment> emailAttachments = new List<Messaging.EmailFileAttachment>();
for(Attachment att : attachments) {

    Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment();
    efa.setFileName(att.Name);
    efa.setBody(att.Body);
    efa.setContentType(att.ContentType);
    emailAttachments.add(efa);
}

email.setFileAttachments(emailAttachments);
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { email });
Ok, I think I have some advancement : but I het Null pointer exception in the 
for(Attachment att : attachments)  as I havent properly written anything in the variable.

What am I doing wrong?
Nitin PaliwalNitin Paliwal
Hi,
Please try this SOQL,
list<Attachment> atts =    [select Id from Attachment where ParentId In (Select Candidate_Contact__c  from Application__c where Job__c = :jobId)];

Please let me know if there is any issue.I hope this solves your problem .

Thanks
NItin
Gaby FrydmanGaby Frydman
Thank you, but I get the following error now :

only aggregate expressions use field aliasing
Nitin PaliwalNitin Paliwal
Hi,
Use this code,

Id jobId = 'a0K24000000QneY';

List<Application__c> existingApplications = new List<Application__c>();
Job__c jobOrder;
List<Id> contactIds = new list<Id>();
list<Attachment> attachments = new list<Attachment>();


if (jobOrder == null) {
 existingApplications = [SELECT Id, Candidate_Contact__c FROM Application__c WHERE Job__c =: jobId];
 for (Application__c appl : existingApplications) {
     contactIds.add(appl.Candidate_Contact__c);               
           
    }
 attachments = [SELECT Id, Name, Body, ContentType FROM Attachment WHere ParentId in :contactIds ];
  }
}
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {'secret@secret.com'};
email.setToAddresses(toAddresses);
email.setSubject('Foo');
email.setPlainTextBody('Bar');

List<Messaging.EmailFileAttachment> emailAttachments = new List<Messaging.EmailFileAttachment>();
for(Attachment att : attachments) {

    Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment();
    efa.setFileName(att.Name);
    efa.setBody(att.Body);
    efa.setContentType(att.ContentType);
    emailAttachments.add(efa);
}

email.setFileAttachments(emailAttachments);
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { email });
 
Gaby FrydmanGaby Frydman
You are brilliant. But a validation stops me with the error : REQUIRED_FIELD_MISSING, No body specified in the file attachment

 
Nitin PaliwalNitin Paliwal
Try this,


Id jobId = 'a0K24000000QneY';

List<Application__c> existingApplications = new List<Application__c>();
Job__c jobOrder;
List<Id> contactIds = new list<Id>();
list<Attachment> attachments = new list<Attachment>();


if (jobOrder == null) {
 existingApplications = [SELECT Id, Candidate_Contact__c FROM Application__c WHERE Job__c =: jobId];
 for (Application__c appl : existingApplications) {
     contactIds.add(appl.Candidate_Contact__c);               
           
    }
 attachments = [SELECT Id, Name, Body, ContentType FROM Attachment WHere ParentId in :contactIds ];
  }
}
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {'secret@secret.com'};
email.setToAddresses(toAddresses);
email.setSubject('Foo');
email.setPlainTextBody('Bar');

List<Messaging.EmailFileAttachment> emailAttachments = new List<Messaging.EmailFileAttachment>();
for(Attachment att : attachments) {
 if(att.body != null){
  Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment();
  efa.setFileName(att.Name);
  efa.setBody(att.Body);
  efa.setContentType(att.ContentType);
  emailAttachments.add(efa);
 }
}

email.setFileAttachments(emailAttachments);
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { email });

 
Gaby FrydmanGaby Frydman
Hi Nitin, 


Interestingly, I still get the same error :

REQUIRED_FIELD_MISSING, No body specified in the file attachment: []

We have defined the email attachment correctly. 
 
Gaby FrydmanGaby Frydman
I have just checked and the problem cmes from the query. any help please?
Nitin PaliwalNitin Paliwal
Which query?