Apex Code Development You need to sign in to do that
Don't have an account?
erik_eklund Need help with SOQL query through junction object
I'm fairly new to Apex (and I'm not a developer by trade) and I'm stumped on a SOQL query for a trigger that I'm writing.
Background info: I'm attempting to set up a process by which we can add Contacts to a custom object called Notification Groups. These Notification Groups can then be associated with Cases, and when a Comment is left on a Case and marked as Public, I want to email the Contacts within the associated Notification Groups. In order to make the necessary many-to-many relationships possible (Contacts can be a part of more than one group, groups can be associated with more than one case), I've created two junction objects: ContactGroupJoiner__c and GroupCaseJoiner__c. They both have Master-Detail relationships with the objects they're joining. Here is a quick graphic to show the relationship:
The trigger fires on CaseCommentI'm able to get the Notification Groups that are associated with the case from the following SOQL query:
List<GroupCaseJoiner__c> notificationGroups = [SELECT Notification_Group__r.Id FROM GroupCaseJoiner__c WHERE Case__c = :caseId];
I'm stuck on the next step, though, which would be to look at each item in the notificationGroups list, grab its related Contacts' email addresses, and add them to a list called toSendEmails (declared earlier in the Class). Here is what I tried:
//Create new list that will hold ContactGroupJoiner records associated with the records in notificationGroups.
List<ContactGroupJoiner__c> contactsInGroups = new List <ContactGroupJoiner__c>();
//For each Group in the notificationGroups list, grab the associated Contact records and store them in contactsInGroups.
for(Integer i=0; i<notificationGroups.size(); i++)
{
contactsInGroups.add( [SELECT Id, Notification_Group__c, Contact__c FROM ContactGroupJoiner__c WHERE Notification_Group__c = :notificationGroups[i].Id] );
}
//Create placeholder list for Contacts to Mail
List<Contact> contactsToMail = new List <Contact>();
//For each contact associated with a group, grab that contact's ID and email address.
for(Integer i=0; i<contactsInGroups.size(); i++)
{
contactsToMail.add( [SELECT Id, Email FROM Contact WHERE Id = :contactsInGroups[i].Id] );
}
system.debug('contactsToMail Size = ' + contactsToMail.size());
//For each contact's email address,
for(Integer i=0; i<contactsToMail.size(); i++)
{
toSendEmails.add(contactsToMail[i].Email);
}
Some of that seems inefficient to me, and probably should be combined. But anyway, the result of this code, when I test it on a case in my sandbox, is that the public comment will be sent to the Contact on the Case (handled earlier in the class), as well as only one contact (of two) associated to one Notification Group (of two) associated with the Case.
I think I've included all the necessary details, but if I haven't please let me know what else would help. Thanks in advance to anyone that reads this far! :)
My first thought was couldn't you use Chatter in some way for this? Okay, so you've thought of that, or it's not appropriate...Let me try to restate the issue to see if I have it right:
1) I have a list of Notification_Group__c that need to be emailed.
2) I need the Contact__c.email of the members of the group in a list, correct?
Can you take the list in 1) above make a list of IDs to use in a query, call it lstGroupId
Can you not say:
[select cgj.Contact__c.email from ContactGroupJoiner__c cgj where cgj.Notification_group__c.id in :lstGroupId]
I haven't tried, but you may not be able to see cgj.Contact__c.email, so if you just get cgj.Contact__c (the Contact Id) you could then put that in a list and query the Contact table for the email directly.
I know that might be an extra step, but that is the route I would try.
I hope that is of some sort of help.
Your restatement of the issue is correct, thanks for the suggestion. I recall trying something similar that gave me an error, but I'm unsure if it was exactly as you laid out. I'll give it a try and let you know how it goes.
I actually hadn't thought of the Chatter option. If there were a way to force a Chatter Group to follow a Case, that would solve my issue. I'm not sure if that's possible, at the moment, though.
Look into "Auto-follow". I have a client who built a trigger so that any contact you create you would automatically be following, and I remember seeing something coming out like this... but be careful - I believe there's a limit to how many "things" you can follow.
I would try the chatter thing out as I hate emails :)
Definitely want to look into the auto-follow idea. I could have it key off of a record type or something.
The follow limit is pretty low, but I use the Chatter Unfollow Rules app on the AppExchange to keep the number of followed items at a minimum. Still hoping that limit gets increased, though.
Ok, the solution to your problem is very simple.
You have to trace it back to the main object. In this case first let me point out, do not include SOQL inside the for loop, that is a crime punishable by a very nasty red message on the clients screen and an awkward moment during testing. No no.
Now coming back to your problem, ill write the steps what you have to do, which is fairly simple.
1. Create a set of ID and populate it with Notification GroupID.
This can be done in the first for loop mentioned below. Lets say NotificationIDSet
2. To populate the ContactsInGroups List, use the set created in step one. This should be done OUTSIDE the for loop. The query would be.
3. Observe the Contact__r.Email parameter in the above query, this actually removes your necessity for a separate query. To add in email set, you can reach the email via Contact__r.email field.
I think this will work.
Cheerio,
Sid
http://force.siddheshkabe.co.in
Thanks for the help, Sid!
Can you lay out the code for your point #1? I thought I was on the right track in modifying mine to match your idea, but I seem to be doing something wrong. Sorry to make you get so specific, I get stuck on the easy stuff sometimes.
List<ContactGroupJoiner__c> contactsInGroups = new List <ContactGroupJoiner__c>(); Set<id> ContactsGroupid= new Set<id>(); //For each Group in the notificationGroups list, grab the associated Contact records and store them in contactsInGroups. for(Integer i=0; i<notificationGroups.size(); i++) { contactsGroupId.add(notificationGroups[i].id); } contactsInGroups =([SELECT Id, Notification_Group__c, Contact__c FROM ContactGroupJoiner__c WHERE Notification_Group__c in :contactsGroupId]);Yes I think this should give an Idea exactly how to do it?
Thanks again, Sid. Sorry, it's been a while since you responded, but I finally got time to return to this project. The code saves without error now, which is more than I had before, however it doesn't seem to be getting to the Contact records still. Here is the code that I have (with an actual Case ID in place of ":caseId":
List<String> toSendEmails = new List<String>(); List<GroupCaseJoiner__c> notificationGroups = [SELECT Notification_Group__r.Id FROM GroupCaseJoiner__c WHERE Case__c = :caseId]; List<ContactGroupJoiner__c> contactsInGroups = new List <ContactGroupJoiner__c>(); Set<id> contactsGroupId = new Set<id>(); for(Integer i=0; i<notificationGroups.size(); i++){ contactsGroupId.add(notificationGroups[i].id); } contactsInGroups = ([SELECT Id, Notification_Group__c, Contact__c, Contact__r.Email FROM ContactGroupJoiner__c WHERE Notification_Group__c in :contactsGroupId]); for(Integer i=0; i<contactsInGroups.size(); i++){ toSendEmails.add(contactsInGroups[i].Contact__r.Email); }And here is the output from the System Log. At 14:31:52.042 I get 0 Rows returned.