Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in Salesforce by (11.9k points)

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!

1 Answer

+1 vote
by (32.1k points)
edited by

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! 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Dec 14, 2020 in Salesforce by dante07 (13.1k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.5k questions

32.6k answers

500 comments

108k users

Browse Categories

...