+ Start a Discussion
Andrew TelfordAndrew Telford 

Join two objects in SOQL

Hi

Iam attempting to reduce the number of SOQL's being triggered to avoid the limits.

I currently have two queiries. The first brings back the mailout items and the second brings back the attachements for the mailout items

Get the mailout items
objMailout = [SELECT Id, contact__c, contact__r.Name, recipient_first_name__c, recipient_last_name__c, caps__r.AccountID__c, file_password__c, email_address__c FROM ci_mailout__c WHERE Sent__c = FALSE AND Mailout_name__c = :strTemplate_Initial ORDER BY Policy_count__C Desc LIMIT 50];
Get the attachments for the above mailout items
objAttachment = [SELECT ID, Name, ContentType, body FROM attachment WHERE ParentId = :thisMailOut.Id];
So ... the above works but gets close to the limit for SOQL

So when I try to join the SOQL into 1 I get the following error
A driving SObject type has already been set, all other entity types in the FROM clause must be relationships to the initial object.  The driving object is CI_Mailout__c.
This is the query that I am attempting to use
objMailout = [SELECT Id, contact__c, contact__r.Name, recipient_first_name__c, recipient_last_name__c, caps__r.AccountID__c, file_password__c, email_address__c FROM ci_mailout__c, attachment WHERE ID = :[SELECT PARENTID FROM ATTACHMENT] AND Sent__c = FALSE AND Mailout_name__c = :strTemplate_Initial ORDER BY Policy_count__C Desc LIMIT 50];

Any guidance would be greatly appreciated.

Thanks


 
Best Answer chosen by Andrew Telford
Virendra ChouhanVirendra Chouhan
Hi Andrew,

Im so sorry, For the parent we have limited to field listed on the Name Object and we cant add other standard or custom fields on it. like:
 
objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.Policy_Count__c FROM attachment]; objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.File_Name__c FROM attachment];
This wan't work.

And also we cant get Attachment body (binary field) in joined query:
[select name,(SELECT ID,Name,body FROM attachments) from Account];
This will not work as well.

You have to use two SOQL to get attachment body and custom object fields.

Sorry :-(

All Answers

Virendra ChouhanVirendra Chouhan
Hi Andrew,

If you want to query on multiple objects, You can do this in 2 way:
either using subquery or using parent relationship name.

In youre case Attachment is child object of ci_mailout__c so you can try this:
 
list<sObject> objectList = [SELECT ID,Name,ContentType,body,parent.Id,parent.contact__c,parent.contact__r.name FROM attachment WHERE ParentId = :thisMailOut.Id];
Note: add as many fields you want in the query.

You also can use subquery but in joined query we can't use binary fields like body of attachment.
 
rohitsfdcrohitsfdc
hello Andrew,

If i understand your query correctly, you want a single query to get Mailout records and their attachments. Since attachments are child of mailout items, you can do query like this (note the bold syntax)
objMailout = [SELECT Id, (select id from attachments), contact__c, contact__r.Name, recipient_first_name__c, recipient_last_name__c, caps__r.AccountID__c, file_password__c, email_address__c FROM ci_mailout__c WHERE Sent__c = FALSE AND Mailout_name__c = :strTemplate_Initial ORDER BY Policy_count__C Desc LIMIT 50];

This will give you all records with their list of attachments.

Hope this answers your query. Mark it resolved, if it does.

Thanks

 
Andrew TelfordAndrew Telford
Thanks for the feedback guys.

@rohitsfdc
I tried that one previously and while what you have will work, I need to get all the data from teh attachment record so that I can attach it to the outgoing email( i.e. body, contentType). If I add more fields from the attachment, I get errors related to adding list to a list.

@Virendra Chouhan
I tried the following but got errors.
PUBLIC LIST <ci_mailout__c> objMailout;
objMailout = [SELECT ID, Name, ContentType, body, parent.id FROM attachment WHERE parent.Mailout_name__r = :strTemplate_Initial];
the error
No such column 'Mailout_name__r' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

I even tried putting in the object name ( CI_Mailout__C ) thinking that 'parent' was being used as a variable.

In theory, I could send out smaller batches so that I don't do so many SOQL but that just means it will take so much longer to run the process. Any futher thoughts?



 
Virendra ChouhanVirendra Chouhan
Hi Andrew,

First thing you cant store list of Attachments into a custom object list so create a list of Attachment.
like:
public list<Attachment> attachmentList;
Second thing dont use __r if you are not accessing related fields just use __c 
attachmentList = [SELECT ID, Name, ContentType, body, parent.id FROM attachment WHERE parent.Mailout_name__c = :strTemplate_Initial];


 
Andrew TelfordAndrew Telford
Thanks for pointing out the oject type was wrong in my declaration Have corrected that but still no joy.
 
PUBLIC LIST <attachment> objMailout;

objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Mailout_name__c FROM attachment WHERE parent.Mailout_name__c = :strTemplate_Initial];

Error:
No such column 'Mailout_name__c' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

The field does exist as per my object ci_mailout__c (summary not all fields)
Singular Label    CI Mailout
Plural Label      CI Mailout
Object Name       CI_Mailout
API Name          CI_Mailout__c

Mailout Name Mailout_Name__c	Picklist

In playing around I have found that this will work
objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name FROM attachment];

But these won't
objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.Policy_Count__c FROM attachment];

objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.File_Name__c FROM attachment];

objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.contact__c.Name FROM attachment];

objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.contact__r.Name FROM attachment];

It would appear attempting to add a custom field from the parent (ci_mailout__c) or referencing up higher than parent just doesn't allow things to work.
 
Virendra ChouhanVirendra Chouhan
Hi Andrew,

Im so sorry, For the parent we have limited to field listed on the Name Object and we cant add other standard or custom fields on it. like:
 
objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.Policy_Count__c FROM attachment]; objMailout = [SELECT ID, Name, ContentType, body, parent.id, parent.Name, parent.File_Name__c FROM attachment];
This wan't work.

And also we cant get Attachment body (binary field) in joined query:
[select name,(SELECT ID,Name,body FROM attachments) from Account];
This will not work as well.

You have to use two SOQL to get attachment body and custom object fields.

Sorry :-(
This was selected as the best answer
Andrew TelfordAndrew Telford
Thanks Virendra Chouhan

Probably why I did it like that in the first place some time ago. Oh well.

Thanks for your time :)