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
AlSawtoothAlSawtooth 

How can I bulkify this code? (SOQL inside for loop)

I'm trying to count the total number of distinct years for which a given account has associated opportunities. This code works fine in individual cases, but breaks during bulk updates. Any ideas?
trigger UpdateOppYears on Opportunity (after update, after insert, after delete) {
if(Trigger.IsUpdate || Trigger.IsInsert){

    List<Account> accs = 
        [SELECT Id, Years_of_Opps__c FROM Account WHERE Id IN
             (SELECT AccountId FROM Opportunity WHERE Id in:Trigger.newMap.keySet() 
              AND AccountId != null
              AND StageName = 'Received')];
    
     for(Account a: accs) {
        AggregateResult[] groupedResult = 
            [SELECT AccountId, COUNT_DISTINCT(CloseYear__c) NumberOfDistinctYears FROM Opportunity o
                WHERE AccountId = :a.Id
                AND o.StageName = 'Received'
                GROUP BY AccountId];
         Decimal aggregateCount = (Decimal)groupedResult[0].get('NumberOfDistinctYears');
         a.Years_of_Opps__c = aggregateCount;
    }
    
    update accs;

 
Best Answer chosen by AlSawtooth
James LoghryJames Loghry
trigger UpdateOppYears On Opportunity (...){

    Set<Id> accountIds = new Set<AccountId>();
    for(Opportunity o : Trigger.new){
        accountIds.add(o.AccountId);
    }

    List<Account> accountsToUpdate = new List<Account>();
     for(AggregateResult ar : [SELECT AccountId, COUNT_DISTINCT(CloseYear__c) NumberOfDistinctYears FROM Opportunity o
                WHERE AccountId in :accountIds
                AND o.StageName = 'Received'
                GROUP BY AccountId]) {
                    accountsToUpdate.add(new Account(Id=ar.get('expr0'),Years_Of_Opps__c=ar.get('NumberOfDistinctYears'));
    }
    
    update accountsToUpdate;
}

^ - Needs a bit of work, but thats generally how you would bulkify.

All Answers

James LoghryJames Loghry
trigger UpdateOppYears On Opportunity (...){

    Set<Id> accountIds = new Set<AccountId>();
    for(Opportunity o : Trigger.new){
        accountIds.add(o.AccountId);
    }

    List<Account> accountsToUpdate = new List<Account>();
     for(AggregateResult ar : [SELECT AccountId, COUNT_DISTINCT(CloseYear__c) NumberOfDistinctYears FROM Opportunity o
                WHERE AccountId in :accountIds
                AND o.StageName = 'Received'
                GROUP BY AccountId]) {
                    accountsToUpdate.add(new Account(Id=ar.get('expr0'),Years_Of_Opps__c=ar.get('NumberOfDistinctYears'));
    }
    
    update accountsToUpdate;
}

^ - Needs a bit of work, but thats generally how you would bulkify.
This was selected as the best answer
AlSawtoothAlSawtooth
Thank you!! What is 'expr0' in line 13?
Dushyant SonwarDushyant Sonwar
In James trigger 'expr0' is alias name.If you don't give any alias, it automatically gives alias name with 'expr' with starting index '0'.
 
AlSawtoothAlSawtooth
Just so I understand - what field are you aliasing with 'expr0' in the code above?

And thank you so much for your help!!
Dushyant SonwarDushyant Sonwar
It's the AccountId which he is aliasing in his trigger.
 
AlSawtoothAlSawtooth
In this line 
accountsToUpdate.add(new Account(Id=ar.get('AcId'),Years_Of_Opps__c=ar.get('NumberOfDistinctYears')));

I'm getting an error that says Compile Error: Invalid initial expression type for field Account.Id, expecting: Id at line 18 column 42. If I add a ".Id" at the end (like below): 
accountsToUpdate.add(new Account(Id=ar.get('AcId').Id,Years_Of_Opps__c=ar.get('NumberOfDistinctYears')));

I'm getting an Compile Error: Initial term of field expression must be a concrete SObject: Object at line 18 column 57 error.

Do you know what I should do here?
James LoghryJames Loghry

get('field') returns a generic object.  You'll need to cast the aggregated fields to their respective types of Id and either Decimal or Integer.  For instance:
 

accountsToUpdate.add(new Account((Id)ar.get('AcId'),Years_Of_Opps__c=(Decimal)ar.get('NumberOfDistinctYears')));
AlSawtoothAlSawtooth
OH that makes so much sense. Thank you so much!!