Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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]
    )
)
share|improve this question
    
Are you trying to avoid performing this action in two queries for any specific reason? (One for accountid values of the contacts which you've sent mail to and a second query for all of the data which you're looking to retrieve?) Can you provide the query you've come up with so far which isn't working for you? –  Mark Pond Jan 17 '13 at 20:55
    
I'm trying to get it all in one query because I want to use it for a Conga Composer document. Here's the code that's giving me a nesting error: 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]')) –  Mark Slayton Jan 17 '13 at 21:01
    
@MarkSlayton Can you please update your answer and delete the comment, it will make it much easier to read –  Daniel Blackhall Jan 17 '13 at 21:08

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
1  
I don't think that your B and C ideas will work; I thought that a semi-join could only query ID fields, which wouldn't work for a formula field that links to an ID. At least, it didn't seem to work when I was trying. –  Mark Slayton Jan 17 '13 at 21:24
    
Mark, that's exactly why I said, in option B, that you'll need to create a separate custom Lookup field to the Account object, called something like AccountId__c, which is automatically populated and kept in sync with the CampaignMember's Contact's Account using an Apex Trigger. You'll have to have a TRUE ID/Reference Field on CampaignMember that points to the Account object in order for this to work. The Trigger just saves you the hassle of having to manually update the value of this field --- since workflow rules can't update Relationship fields. –  zachelrath Jan 18 '13 at 0:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.