You need to sign in to do that
Don't have an account?
Andrew 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
So when I try to join the SOQL into 1 I get the following error
Any guidance would be greatly appreciated.
Thanks
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
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:
This wan't work.
And also we cant get Attachment body (binary field) in joined query: This will not work as well.
You have to use two SOQL to get attachment body and custom object fields.
Sorry :-(
All Answers
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:
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.
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)
This will give you all records with their list of attachments.
Hope this answers your query. Mark it resolved, if it does.
Thanks
@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. the error
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?
First thing you cant store list of Attachments into a custom object list so create a list of Attachment.
like: Second thing dont use __r if you are not accessing related fields just use __c
Error:
The field does exist as per my object ci_mailout__c (summary not all fields)
In playing around I have found that this will work
But these won't
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.
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:
This wan't work.
And also we cant get Attachment body (binary field) in joined query: This will not work as well.
You have to use two SOQL to get attachment body and custom object fields.
Sorry :-(
Probably why I did it like that in the first place some time ago. Oh well.
Thanks for your time :)