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.
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...
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...
You could use a semi-join on contacts to filter the contacts to the set you want, something 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 would be to drive from campaignMmeber instead.
[select contact.id, contact.firstname, contact.lastname,
status, comment__c, campaignId from campaignMembers
where contactId !='' and campaignId in :cams];