1

How can I count how many contact child related to account object and total count of child to show on a custom field which created on account object using a trigger?

trigger AccTrigger on Account (before insert,before update) {
        system.debug('Trigger');
        set<id> getId = new set<id>();
        public list<Account>AccList = new list<Account>();
      for(account ac : trigger.new){
          getId.add(ac.id);          
      } 
      map<id,contact> ConMap = new map<id,contact>([select id,name from contact where id IN:getId]);
}

3 Answers 3

1
trigger ContactTrigger on contact (after insert,after delete,after update,after undelete) {

Set<String> setOfAccount=new Set<String>();
if(Trigger.isInsert || trigger.isUndelete){
    for(Contact con:Trigger.new){
        setOfAccount.add(con.accountId);
    }
}else if(Trigger.isDelete || Trigger.isUpdate){
    for(Contact con:Trigger.old){
        setOfAccount.add(con.accountId);
    }
}
if(trigger.isUpdate){
    for(contact con:trigger.new){
        setOfAccount.add(con.accountId);
    }
}
List<account> listOfAccount=new List<Account>([select id,(select id from contacts)from account where id=:setOfAccount]);
for(account acc:listOfAccount){
    acc.Number_of_Child_Contact__c=acc.contacts.size();
}
update listOfAccount;

}

0
1

You can do this by inner query as:

List<Account> accountWithContact = [SELECT id, (Select id FROM Contact) FROM Account WHERE ID IN: getId];

for(Account acc : accountWithContact)
{
   acc.Count_Fied__c = acc.Contacts!=null ? acc.Contacts.size() : 0;
}

Another way is by Aggregate query as:

AggregateResult[] groupedResults = [SELECT COUNT(Id), AccountID FROM Contact where AccountID IN :getId GROUP BY AccountID ];

I hope there are not millions of Accounts with 100 thousand child contacts. Otherwise you will get selective query issue.

1
  • You'll run into a Aggregate Query has too many rows for direct assignment: use for loop error with the inner query long before running into a query selectivity issue. I like the inner query better, but I'd say the second one using COUNT(Id) is marginally safer. You also don't need to do the null check on acc.Contacts as it's literally a List<Contact> (which will have size 0 if no Contacts exist for the Account)
    – Derek F
    Commented May 16, 2017 at 16:32
1

You could use a GROUP BY clause in your SOQL query. This will return an AggregateResult that contains AccountId and ContactCount. For ease of use, I converted that into a Map<Id, Integer>. Lastly, iterate the trigger Accounts and use the map to lookup the contact count.

// copy Ids to a list
List<Id> ids = new List<Id>();
for (account ac : trigger.new) {
    ids.add(ac.id);          
}

// run an aggreate query
AggregateResult[] agg =
    [select AccountId, COUNT(Id) ContactCount
     from Contact
     where AccountId in :ids
     group by AccountId];

// convert the aggregate query to a map
Map<Id, Integer> contactCountsByAccountId = new Map<Id, Integer>();
for (AggregateResult ar : agg)  {
    contactCountsByAccountId.put((Id)ar.get('AccountId'), (Integer)ar.get('ContactCount'));
}

// set the custom field
for (Account ac : trigger.new) {
    if (contactCountsByAccountId.containsKey(ac.Id)) {
        // the Account has contacts
        ac.MyContactCount__c = contactCountsByAccountId.get(ac.Id);
    } else {
        // the Account does not have contacts
        ac.MyContactCount__c = 0;
    }
}

The nice thing about using GROUP BY is that the result set will never be larger than the number of records in the trigger.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .