9

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...
  1. Why aren't any CampaignMember objects being returned from the subquery?
  2. 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...

skaffman
  • 398,947
  • 96
  • 818
  • 769
Benj
  • 1,853
  • 4
  • 18
  • 27
  • SQL subqueries in the SELECT aren't allowed to return more than one column – OMG Ponies Oct 26 '10 at 19:37
  • Why you use a SQL subquery there? You can make a join query between the 2 tables!! As Omg Ponies said, the subquery on the select just can return 1 column per subquery!! – Nervo Verdezoto Oct 26 '10 at 19:54
  • 8
    This is salesforce **SOQL**, not SQL. There is no JOIN keyword in SOQL - you can only traverse relationships using subqueries (which do return more than one column). – Benj Oct 26 '10 at 19:58

1 Answers1

20

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];
superfell
  • 18,780
  • 4
  • 59
  • 81
  • Looks promising -- i won't have a chance to try for a few days but will check back. Thanks! – Benj Oct 28 '10 at 05:48
  • Perfect! Thanks. I ended up going w/ the first option, which is just one query (!) for governor limits, and autogroups the CampaignMembers by Contact which is convenient. – Benj Oct 29 '10 at 15:42
  • Can you tell me that how to create campaign through ZKSfORCE api? – Umair_uas Nov 17 '11 at 15:45
  • Can we update objects like these in Apex?? Does it Returns Sobject or contact – Shanker Paudel Mar 13 '14 at 14:59