function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

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++)

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 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 from ContactGroupJoiner__c cgj where in :lstGroupId]


I haven't tried, but you may not be able to see, 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.


contactsInGroups= new List<ContactGroupJoiner__c>([SELECT Id, Notification_Group__c, Contact__c, Contact__r.Email FROM ContactGroupJoiner__c WHERE Notification_Group__c in : NotificationIDSet );


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 field.


I think this will work. 





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.

The RavanThe Ravan
   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++)
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++){
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++){



And here is the output from the System Log. At 14:31:52.042 I get 0 Rows returned.


14:31:52.030 (30991000)|EXECUTION_STARTED
14:31:52.031 (31007000)|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
14:31:52.032 (32272000)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select Notification_Group__r.Id from GroupCaseJoiner__c where Case__c = ---redacted Case Id---
14:31:52.037 (37365000)|SOQL_EXECUTE_END|[2]|Rows:2
14:31:52.037 (37768000)|SYSTEM_METHOD_ENTRY|[6]|LIST.size()
14:31:52.037 (37828000)|SYSTEM_METHOD_EXIT|[6]|LIST.size()
14:31:52.037 (37943000)|SYSTEM_METHOD_ENTRY|[7]|SET.add(ANY)
14:31:52.037 (37981000)|SYSTEM_METHOD_EXIT|[7]|SET.add(ANY)
14:31:52.037 (37995000)|SYSTEM_METHOD_ENTRY|[6]|LIST.size()
14:31:52.038 (38005000)|SYSTEM_METHOD_EXIT|[6]|LIST.size()
14:31:52.038 (38045000)|SYSTEM_METHOD_ENTRY|[7]|SET.add(ANY)
14:31:52.038 (38058000)|SYSTEM_METHOD_EXIT|[7]|SET.add(ANY)
14:31:52.038 (38068000)|SYSTEM_METHOD_ENTRY|[6]|LIST.size()
14:31:52.038 (38076000)|SYSTEM_METHOD_EXIT|[6]|LIST.size()
14:31:52.038 (38600000)|SOQL_EXECUTE_BEGIN|[10]|Aggregations:0|select Id, Notification_Group__c, Contact__c, Contact__r.Email from ContactGroupJoiner__c where Notification_Group__c = :tmpVar1
14:31:52.042 (42373000)|SOQL_EXECUTE_END|[10]|Rows:0
14:31:52.042 (42459000)|SYSTEM_METHOD_ENTRY|[13]|LIST.size()
14:31:52.042 (42496000)|SYSTEM_METHOD_EXIT|[13]|LIST.size()
14:31:52.112 (42545000)|CUMULATIVE_LIMIT_USAGE
Number of SOQL queries: 2 out of 100
Number of query rows: 2 out of 50000
Number of SOSL queries: 0 out of 20
Number of DML statements: 0 out of 150
Number of DML rows: 0 out of 10000
Number of script statements: 10 out of 200000
Maximum heap size: 0 out of 6000000
Number of callouts: 0 out of 10
Number of Email Invocations: 0 out of 10
Number of fields describes: 0 out of 100
Number of record type describes: 0 out of 100
Number of child relationships describes: 0 out of 100
Number of picklist describes: 0 out of 100
Number of future calls: 0 out of 10


14:31:52.042 (42578000)|CODE_UNIT_FINISHED|execute_anonymous_apex
14:31:52.042 (42587000)|EXECUTION_FINISHED