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 aren't any CampaignMember objects being returned from the subquery?- 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!
Update
The CampaignMember objects are now showing up - oddly - I must have fixed some small typo without noticing (and yes, they're returning multiple columns and that seems to be fine).
I still can't figure out how to filter the Contact query, though...