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.)
Campaign cams = [SELECT id, name
WHERE parentid = '70170000000LRIe'];
// returns ~4 Campaign objects
CampaignMember cmembers = [SELECT id, status, contactid, campaignid
WHERE campaignid in :cams];
// returns about 40 CampaignMember objects.
Here's my problem:
Contact members = [SELECT id, firstname, lastname,
(SELECT id, status, comment__c, campaignid
WHERE campaignid in :cams)
// contains ALL Contacts in the DB, but I wanted filtered results.
// 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.