function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Florian BouletFlorian Boulet 

Process result of SOQL query

Hello,

I'm getting the following data :
SELECT 
  Id,
  Contact.Id,
  Contact.Name,
  Contact.AccountId,
  Contact.Account.Name 
FROM CampaignMember
WHERE Campaign.Type = 'Programme Application'
Now for each row I would like the count of each contact in the account with a CampaignMember.
SELECT 
  COUNT()  
FROM CampaignMember
WHERE Contact.AccountId = $$CURRENT_CONTACT_ACCOUNT$$
I tried the following but it doesn't works :
SELECT 
  Id,
  Contact.Id,
  Contact.Name,
  Contact.AccountId,
  Contact.Account.Name,
  (SELECT 
       COUNT()  
   FROM CampaignMember cm2
   WHERE cm2.Contact.AccountId = cm.Contact.AccountId) as countMDC  
FROM CampaignMember cm
WHERE Campaign.Type = 'Programme Application'
With APEX i tried to parse every row of the first query to run the second query, but i can't access the Contact.AccountId value of each row. The returned object is a CampaignMember with only ContacId, CampaignMemberID and CampaignId.
for(SObject currentContact : [SELECT 
	                          Id,
							  Contact.Id,
							  Contact.Name,
							  Contact.AccountId,
							  Contact.Account.Name 
							  FROM CampaignMember
							  WHERE Campaign.Type = 'Programme Application']){
			System.debug(currentContact);
}
The foreach result
Have you any idea how to do it ? It looks very simple problem but i'm stuck since few hours now ... oO'
Best Answer chosen by Florian Boulet
Bryan LeamanBryan Leaman
I don't understand your use-case for this, but you'll need to get your other SOQL query outside the for-loop. Also, I'm assuming (maybe incorrectly) that your inner SOQL should be limited to the campaign member records you initially selected. I don't understand why you'd want to query all campaigns for all time to get the contact count. Frankly, I don't understand why you'd initially want to query all campaigns for all time for a specific campaign type either.

It sounds like you want each campaign member to indicate the total number of contacts in the campaign that have the same account as the contact on the campaign member record. To store a value on an SObject record, even if you're not saving it to the CampaignMember object, you'll need that field to exist on the SObject. So *if* you create a "contactCount" field on the CampaignMember object, then you code code it like this:
 
@AuraEnabled
	public static List<Object> getContactCMCount() {
		List<SObject>  result = new List<SObject>();
		result = [SELECT
					Id,
                    contactCount, // you need the field to be retrieved in the query to access it
					Contact.Id,
					Contact.Name,
					Contact.AccountId,
					Contact.Account.Name 
				FROM CampaignMember
				WHERE Campaign.Type = 'Programme Application'
			];

        // Create a map of Account Id & Count of contacts & initialize to 0
        Map<Id, Decimal> contactCountMap = new Map<Id, Decimal>();
        for(CampaignMember cm : result) {
           contactCountMap.put(cm.Contact.AccountId, 0);
        }

        // Query contact members and group by contact's accountid to update the map
        for(AggregateResult ar : [
            SELECT Contact.AccountId accountid, count(Id) nbrcontacts
            FROM CampaignMember
            WHERE Campaign.Type = 'Programme Application' // or not, if you want all campaigns
              AND Contact.AccountId in :contactCountMap.keySet()
        ]) {
           contactCountMap.put((Id) ar.get('accountid'), (Decimal) ar.get('nbrcontacts'));
        }

        // Assign contactCount to each campaign member		
		for(SObject currentCM : result){
            Contact currentContact = currentCM.getSObject('Contact'); 
            currentCM.put('contactCount', contactCountMap.get(currentContact.AccountId));
		}

		return result;
	}

If you don't want the new field in the CampaignMember object, then you'll need to create a wrapper object that has the CampaignMember record as one member (or each element of CampaignMember you need) and the count as another.  For example (I've renamed the initial SOQL results because it's no longer the result that is returned):
@AuraEnabled
	public static List<Object> getContactCMCount() {
		List<SObject>  campaignmbrs = new List<SObject>();
		campaignmbrs = [SELECT
					Id,
                    contactCount, // you need the field to be retrieved in the query to access it
					Contact.Id,
					Contact.Name,
					Contact.AccountId,
					Contact.Account.Name 
				FROM CampaignMember
				WHERE Campaign.Type = 'Programme Application'
			];

        // Create a map of Account Id & Count of contacts & initialize to 0
        Map<Id, Decimal> resultMap = new Map<Id, Decimal>();
        for(CampaignMember cm : campaignmbrs) {
           resultMap.put(cm.Contact.AccountId, new CampaignMemberWrapper(cm, 0) );
        }

        // Query contact members and group by contact's accountid to update the map
        for(AggregateResult ar : [
            SELECT Contact.AccountId accountid, count(Id) nbrcontacts
            FROM CampaignMember
            WHERE Campaign.Type = 'Programme Application' // or not, if you want all campaigns
              AND Contact.AccountId in :contactCountMap.keySet()
        ]) {
           CampaignMemberWrapper wrap = resultMap.get((Id) ar.get('accountId'));
           wrap.contactCount = (Decimal) ar.get('nbrcontacts');
        }

		return resultMap.values();
	}

// wrapper class to accomodate a count of contacts with the same account as the campaign member
public class CampaignMemberWrapper {
    CampaignMember campaignMbr {get;set;}
    Decimal contactCount {get;set;}
    public CampaignMemberWrapper(CampaignMember cm, Decimal cnt) {
        campaignMbr = cm;
        contactCount = cnt;
    }
}

 

All Answers

Florian BouletFlorian Boulet
Hello,

I'm sorry but i don't understand your reply.
Bryan Leaman 6Bryan Leaman 6
I can't read the error -- it's too small. But I'm wondering why your for loop is over SObject instead of over CampaignMember. Using something like "for(CampaignMember cm : [your soql query]) {}" would allow you to drill into cm.Contact.AccountId. 

When accessing related fields from a generic SObject as you have it coded, you have to retrieve each piece separately. So you would have to access it like this:
 
for(Sobject campaignmbr : [
    SELECT Id, Contact.Name, Contact.AccountId     // etc.
    FROM CampaignMember 
    WHERE Campaign.Type = 'Programme Application']){
        Contact c = (Contact) campaignmbr.getSObject('Contact');
        Id accountid = c.AccountId;
        // or if you don't want to cast the contact, but use another SObject, then like this:
        SObject contct = campaignmbr.getSObject('Contact');
        Id acctid = (Id) contct.get('AccountId');
}

If you have a single campaign Id you're interested in you can get the number of contacts for each account with something like this:
SELECT Contact.AccountId, COUNT(Id)
FROM CampaignMember
WHERE CampaignId='7011T000001ctBgQAI'
GROUP BY CampaignId, Contact.AccountId

​​​​​​​
 
Florian BouletFlorian Boulet
Thank you for your help.
I was missing the ".getSObject()" function. Now i can manage to access the good data.

But I still need help tp aggregate data.

Here my function :
	@AuraEnabled
	public static List<Object> getContactCMCount() {
		List<SObject>  result = new List<SObject>();
		result = [SELECT
					Id,
					Contact.Id,
					Contact.Name,
					Contact.AccountId,
					Contact.Account.Name 
				FROM CampaignMember
				WHERE Campaign.Type = 'Programme Application'
			];
		
		for(SObject currentCM : result){
			Contact currentContact = currentCM.getSObject('Contact');
			Id accountId = currentContact.AccountId;
			Integer contactCount = [SELECT COUNT()
									FROM CampaignMember
									WHERE Contact.AccountId = :accountId]
			
			//I would like to add the data contactCount in the current result row.
			// How to do something like bellow ?
			currentCM.put('contactCount', contactCount);
            // In JS I could do that
            currentCM['contactCount'] = contactCount;
		}
		return result;
	}
Thanks you for your help.

Florian
 
Bryan LeamanBryan Leaman
I don't understand your use-case for this, but you'll need to get your other SOQL query outside the for-loop. Also, I'm assuming (maybe incorrectly) that your inner SOQL should be limited to the campaign member records you initially selected. I don't understand why you'd want to query all campaigns for all time to get the contact count. Frankly, I don't understand why you'd initially want to query all campaigns for all time for a specific campaign type either.

It sounds like you want each campaign member to indicate the total number of contacts in the campaign that have the same account as the contact on the campaign member record. To store a value on an SObject record, even if you're not saving it to the CampaignMember object, you'll need that field to exist on the SObject. So *if* you create a "contactCount" field on the CampaignMember object, then you code code it like this:
 
@AuraEnabled
	public static List<Object> getContactCMCount() {
		List<SObject>  result = new List<SObject>();
		result = [SELECT
					Id,
                    contactCount, // you need the field to be retrieved in the query to access it
					Contact.Id,
					Contact.Name,
					Contact.AccountId,
					Contact.Account.Name 
				FROM CampaignMember
				WHERE Campaign.Type = 'Programme Application'
			];

        // Create a map of Account Id & Count of contacts & initialize to 0
        Map<Id, Decimal> contactCountMap = new Map<Id, Decimal>();
        for(CampaignMember cm : result) {
           contactCountMap.put(cm.Contact.AccountId, 0);
        }

        // Query contact members and group by contact's accountid to update the map
        for(AggregateResult ar : [
            SELECT Contact.AccountId accountid, count(Id) nbrcontacts
            FROM CampaignMember
            WHERE Campaign.Type = 'Programme Application' // or not, if you want all campaigns
              AND Contact.AccountId in :contactCountMap.keySet()
        ]) {
           contactCountMap.put((Id) ar.get('accountid'), (Decimal) ar.get('nbrcontacts'));
        }

        // Assign contactCount to each campaign member		
		for(SObject currentCM : result){
            Contact currentContact = currentCM.getSObject('Contact'); 
            currentCM.put('contactCount', contactCountMap.get(currentContact.AccountId));
		}

		return result;
	}

If you don't want the new field in the CampaignMember object, then you'll need to create a wrapper object that has the CampaignMember record as one member (or each element of CampaignMember you need) and the count as another.  For example (I've renamed the initial SOQL results because it's no longer the result that is returned):
@AuraEnabled
	public static List<Object> getContactCMCount() {
		List<SObject>  campaignmbrs = new List<SObject>();
		campaignmbrs = [SELECT
					Id,
                    contactCount, // you need the field to be retrieved in the query to access it
					Contact.Id,
					Contact.Name,
					Contact.AccountId,
					Contact.Account.Name 
				FROM CampaignMember
				WHERE Campaign.Type = 'Programme Application'
			];

        // Create a map of Account Id & Count of contacts & initialize to 0
        Map<Id, Decimal> resultMap = new Map<Id, Decimal>();
        for(CampaignMember cm : campaignmbrs) {
           resultMap.put(cm.Contact.AccountId, new CampaignMemberWrapper(cm, 0) );
        }

        // Query contact members and group by contact's accountid to update the map
        for(AggregateResult ar : [
            SELECT Contact.AccountId accountid, count(Id) nbrcontacts
            FROM CampaignMember
            WHERE Campaign.Type = 'Programme Application' // or not, if you want all campaigns
              AND Contact.AccountId in :contactCountMap.keySet()
        ]) {
           CampaignMemberWrapper wrap = resultMap.get((Id) ar.get('accountId'));
           wrap.contactCount = (Decimal) ar.get('nbrcontacts');
        }

		return resultMap.values();
	}

// wrapper class to accomodate a count of contacts with the same account as the campaign member
public class CampaignMemberWrapper {
    CampaignMember campaignMbr {get;set;}
    Decimal contactCount {get;set;}
    public CampaignMemberWrapper(CampaignMember cm, Decimal cnt) {
        campaignMbr = cm;
        contactCount = cnt;
    }
}

 
This was selected as the best answer
Bryan LeamanBryan Leaman
Oh, I see I left the "contactCount" field in the second version that uses a wrapper class, and it doesn't need to be there -- that was the point of creating the wrapper class. Oops.
Bryan LeamanBryan Leaman
Double-oops -- I forgot that the wrapper class will need @AuraEnabled decorator on the 2 class members:
// wrapper class to accomodate a count of contacts with the same account as the campaign member
public class CampaignMemberWrapper {
    @AuraEnabled
    CampaignMember campaignMbr {get;set;}
    @AuraEnabled
    Decimal contactCount {get;set;}
    public CampaignMemberWrapper(CampaignMember cm, Decimal cnt) {
        campaignMbr = cm;
        contactCount = cnt;
    }
}

 
Florian BouletFlorian Boulet
So I have to create a custom field or a custom class. I'm gonna try with the custom class.

Thank you very much for your anwsers and your time !