Working Around SOQL Nesting Limits

Question

I'm trying to solve a problem that can be summarized as follows. I need a SOQL query which returns all Contacts on a set of Accounts, so long as any one Contact on that Account is associated with a Campaign Member for a specific Campaign. So if I sent out an e-mail about my Widgets to a contact each at Acme, Bobco and Compududes, the query would return ALL contacts under the Acme, Bobco and Compududes accounts. The problem that I'm having is that I can't double-nest within a WHERE condition. Does anyone know of a workaround for this, or a way I could get the data I need in a single query?

The code that I have that's currently NOT working looks like this:

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]
    )
)

Answer 1

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.