I think there are various ways you could achieve this, depending on what query criteria you already have, and the ideal data structure that you'd like to return. Here are some possible examples:
(a) Say you have the Ids of the Contacts at Acme, Bobco, and Compududes already, or even the CampaignMember records, from which you could extract these (perhaps from an EmailService or Trigger), then you can do this:
// Extract the ContactIds from CampaignMember records
Set<Id> contactIds = new Set<Id>();
for (CampaignMember cm : Trigger.new) {
contactIds.add(cm.ContactId);
}
}
List<Account> accounts = [
select Id, Name, (select FirstName, LastName from Contacts order by LastName, FirstName)
from Account
where Id in (select AccountId from Contact where Id in :contactIds)
];
and then you could iterate over the Accounts and their child Contacts.
(b) If you don't have the Contact Ids yet, i.e. you need to figure them out by querying on the CampaignMember
object, and you absolutely can't use another query (i.e. you're in Conga Composer), then what you'd have to do is add an AccountId__c
field on your CampaignMember
object and use a Trigger to keep this in sync with the AccountId
of the Contact of the CampaignMember
record:
List<Account> accounts = [
select Id, Name, (select FirstName, LastName from Contacts order by LastName, FirstName)
from Account
where Id in (
select AccountId__c
from CampaignMember
where Campaign.Name = 'Mass Membership Drive'
)
];
The reason you have to have a Trigger to populate this is that you have to use foreign key fields when doing SOQL joins (i.e. the field immediately after 'select' cannot be a formula field, otherwise you could just make your AccountId__c
field be a formula field whose formula is Contact.AccountId
. And you also can't do select Contact.AccountId
in a SOQL join. So you have to have a Trigger keep the AccountId in sync.
// ONLY WORKS if `AccountId__c` is NOT a Formula Field
List<Account> accounts = [
select Id, Name, (select FirstName, LastName from Contacts order by LastName, FirstName)
from Account
where Id in (
select AccountId__c
from CampaignMember
where Campaign.Name = 'Mass Membership Drive'
)
];
But (a) will work fine as long as you can access the ContactIds.
SELECT Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Id IN (SELECT ContactId FROM CampaignMember WHERE CampaignID = '[ID]'))