function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
pdopdo 

Help Bulkify

Hi All,

I am attempting to peform a few things with the below trigger and I am getting errors on having too many SOQL queries.  Essentially I need to bulkify this and am looking for advice on how to clean this up:

trigger AccountChildRollup on account (after delete, after update, after insert) {
    
    set<id> accountids = new set<id>();
    list<account> accounttoupdate = new list<account>();
    list<account> childupdate = new list<account>();
    integer childcount;
    integer livechildcount;
    string practiceid;
    
    if (trigger.isinsert) {
        for (account child : trigger.new)
            if (child.parentid != null) {
            accountids.add(child.parentid);
            aggregateresult childacc = [select count(id) from account where parentid != null and parentid in :accountids];
            childcount = integer.valueof(childacc.get('expr0'));
            aggregateresult livechildacc = [select count(id) from account where type = 'Live' and parentid != null and parentid in :accountids];
            livechildcount = integer.valueof(livechildacc.get('expr0'));
            }
    }


    if (trigger.isupdate || trigger.isdelete) {
        for (account child : trigger.old)
            if (child.parentid != null) {
            accountids.add(child.parentid);
            aggregateresult childacc = [select count(id) from account where parentid != null and parentid in :accountids];
            childcount = integer.valueof(childacc.get('expr0'));
            aggregateresult livechildacc = [select count(id) from account where type = 'Live' and parentid != null and parentid in :accountids];
            livechildcount = integer.valueof(livechildacc.get('expr0'));
            }
    }
        
    if (childcount > 0) {   
        map<id,account> parentmap = new map<id,account>([select parentid, id, button_validate__c, count_child_accounts__c, validation_override1__c, validation_override__c from account where parentid = null and id in :accountids]);           
        for (account[] accpracticeid : [select id, practiceid__c from account where parentid in :accountids limit 1]){
            practiceid = accpracticeid[0].practiceid__c;
        }   
        if (!AccountChildRollupRecursiveHelper.hasalreadyupdated()) {
            for (account a : [select id, validation_override__c, button_validate__c from account where parentid = null and id in :accountids]){    
            parentmap.get(a.id).count_child_accounts__c = childcount;
            parentmap.get(a.id).count_live_child_accounts__c = livechildcount;
            parentmap.get(a.id).validation_override__c = datetime.now();
            parentmap.get(a.id).button_validate__c = datetime.now();
            a.validation_override__c = datetime.now();
            a.button_validate__c = datetime.now();
                if (childcount > 0 && livechildcount < 1){
                    parentmap.get(a.id).this_is_a_parent_account__c = true;
                } else {
                    if (livechildcount > 0){
                        aggregateresult golivedate = [select min(go_live_date__c), phreesia_server_version__c from account where type = 'Live' and go_live_date__c != null and parentid in :accountids group by phreesia_server_version__c limit 1];        
                        date mingolivedate = date.valueof(golivedate.get('expr0'));
                        string server = string.valueof(golivedate.get('phreesia_server_version__c'));        
                        parentmap.get(a.id).type = 'Live';
                        parentmap.get(a.id).go_live_date__c = mingolivedate;
                        parentmap.get(a.id).this_is_a_parent_account__c = true;
                        parentmap.get(a.id).phreesia_server_version__c = server;
                    }
                }
            childupdate.add(a);
            accounttoupdate.add(parentmap.get(a.id));
            }
        AccountChildRollupRecursiveHelper.setalreadyupdated();
        update childupdate;
        update accounttoupdate;
        }
    }
}

 

JBabuJBabu

Hi,

 

Don't use select queries inside the for loop.  Instead get the required output using Maps (collections).

 

Thanks,

Babu.
pdopdo

I understand the issue, but I do not understand where where in my trigger, I need to use the map and how to use it.   I am currently using mas, but it looks lik that is not sufficing.  Con you pinpoint the pieces of code I need to focus on?
Starz26Starz26

Look in every one of your for loops. The [Select...... statements cannot be there.

 

Instead, use maps to gather the records before performing logic in a for loop. Use the values in the for loop to 'GET' the records in a map:

 

**BAD**

 

for(Contact c : trigger.new){

      Account[] a = [Select ID From Account Where ID = c.AccountID];

}

 

**BETTER*

 

Map<ID,Account> mAcct;

ID[] acctIDs = New ID[]{};

 

for(Contact c : trigger.new)

      acctIDs.add(c.AccountID);

 

mAcct = New Map<Account>([Select ID From Account Where ID IN :acctIDs]);

 

for(Contact c : trigger.new){

       if(mAcct.get(c.AccountID).ID = ................

}
pdopdo

Hey Starz26, I really appreciate the help!  So I have spent the past few hours trying to figure this out and I cannot seem to make this work.  Below is my most recent attempt using the maps:

trigger AccountChildRollup3 on account (after delete, after update, after insert) {
    list<account> parentupdate = new list<account>();
    id[] pid = new id[]{};
    integer childcount;
    integer livechildcount;
    aggregateresult children;
    aggregateresult livechildren;
    map<id,account> pmap;

    if (trigger.isupdate || trigger.isdelete){
        for (account child : trigger.new)
            pid.add(child.parentid);
            pmap = new map<id,account>([select id from account where id in: pid limit 1]);
            children = [select count(id) from account where parentid in: pid];
            livechildren = [select count(id) from account where type = 'Live' and parentid in: pid];
            childcount = integer.valueof(children.get('expr0'));
            livechildcount = integer.valueof(livechildren.get('expr0'));
    }
    for (account child : trigger.new){
    pmap.get(child.parentid).count_live_child_accounts__c = livechildcount;
    parentupdate.add(pmap.get(child.parentid));
    }
    update parentupdate;
}

 

However, now I am getting this error "Error:Apex trigger AccountChildRollup3 caused an unexpected exception, contact your administrator: AccountChildRollup3: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 0015000000YEVPkAAP; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, AccountChildRollup3: execution of AfterUpdate caused by: System.NullPointerException: Attempt to de-reference a null object Trigger.AccountChildRollup3: line 20, column 1: []: Trigger.AccountChildRollup3: line 23, column 1"

 

I am not sure I get the "if(mAcct.get(c.AccountID).ID = ................" part of your suggestion as all I am trying to do is count child account records and update a field on a parent account (like a roll-up count field).  But cannot get this to work.

 

Any suggestions from here?  What am I not getting?