SOQL query with subquery

Question

I'm having trouble getting the results I want from a Salesforce/Apex/SOQL query.

I want: A list of Contact objects containing only contacts who are CampaignMembers of a set of campaigns; and they should have the data from that Campaign member easily accessible. (my eventual goal is a VF page with a list of all Contacts connected to any of these campaigns with a grid indicating their status for each campaign.)

These work:

Campaign[] cams = [SELECT id, name 

                     FROM Campaign 

                    WHERE parentid = '70170000000LRIe'];  

System.debug(cams);  

// returns ~4 Campaign objects

CampaignMember[] cmembers = [SELECT id, status, contactid, campaignid 

                               FROM CampaignMember 

                              WHERE campaignid in :cams];  

System.debug(cmembers);  

// returns about 40 CampaignMember objects.

Here's my problem:

Contact[] members = [SELECT id, firstname, lastname, 

                            (SELECT id, status, comment__c, campaignid 

                               FROM Contact.CampaignMembers 

                              WHERE campaignid in :cams) 

                       FROM Contact];  

System.debug(members);  

// contains ALL Contacts in the DB, but I wanted filtered results.

System.debug(members[x].CampaignMembers);

// this is a contact I've verified has a qualifying CampaignMember, but the list is empty.

// UPDATE: CampaignMembers are now being returned, not sure what changed...

Why isn't the Contact list being filtered? (well, obviously b/c there's no  WHERE clause in it, but what WHERE clause provides what I want?)

I know I could do this by doing the CampaignMember query on its own and looping through it to prep a Contact query, but that seems like a lot of extra processing when a subquery should work.

Thanks!

Answer 1

You could either use a semi-join on contacts to filter the contacts to the set you want, like this:

[select id, firstname, lastname, 

     (select id, status, comment__c, campaignid from CampaignMembers)

     from contact where id in 

          (select contactId from campaignMember where campaignId in :cams];

Another option is to drive from campaignMember instead like this:

[select contact.id, contact.firstname, contact.lastname, 

  status, comment__c, campaignId from campaignMembers 

  where contactId !='' and  campaignId in :cams];

Look at this Salesforce Course provided by Intellipaat!