2
\$\begingroup\$

I had posted a question some time back for the best way of doing an account hierarchy, and this is the way implemented at last.

Can someone point out any obvious flaw in this (other than SOQL)?

The use case is to find the first account which has IS VGE flag checked, or if no IS VGE ones in hierarchy get the topmost one.

Once you get the record, update some fields with this records' value. If no parent is present, don't bother. There's no business limit in hierarchy levels.

The below method is called by a before insert and before update triggers:

    public with sharing class Find {
//Static variable to make sure method is called only once
public static boolean firstRun_GlobalUltimate = true;   
public static void  findGlobal(list<account>Triggerlist)
{
    map<Integer,id> Treemap = new map<Integer,ID>();
    account a = new account();
    set<ID> parentIdSet = new set<ID>();
    Map<ID,Account>parentRec= new map<ID,Account>();
    Id accid;
    account gm;
    map<ID,Account>queryMap =  new map<ID,Account>();
//Forming a map of current Account indexes and their parents,if there is any    
    for (integer n=0; n< TriggerList.size(); n++) 
    {
        //Checking if Parent is missing for the record
        If (TriggerList[n].ParentId != null) 
            {
                //Add key for map as index of item in list,Value as Parent ID present for Account
                treeMap.put(n, TriggerList[n].ParentId);
                //Add parent id  to parentIdset to be used in account heirarchy loop
                parentIdSet.add(TriggerList[n].ParentId);
            }

    }//End of main for loop                 


//Account hierarchy traversy Loop:Applicable only to accounts with a parent
//Once loop is executed Treemap will be updated with either
//first VGE account it met in hierarchy or the top most account in heirarchy
    do
        {
            //Clear the querymap                                     
            querymap.clear();
            //Select parent record with Is vge account and its parentid
            queryMap = new map<ID,Account>([select Is_VGE_Account__c ,Segment__c,Public_sector__c,parentid from account where id in :parentidset]);
            //Clear parentidset
            parentidset.clear();
            //check whether map is empty
            if(!querymap.isempty())
                {
                    //Traverse through the treemap
                    for(integer i:treemap.keyset())
                        {
                            accid = treemap.get(i);
                            if(querymap.containskey(accid) )
                                {   //Get the parent record
                                    gm = querymap.get(accid);
                                    //Form a  map of all parent record
                                    parentRec.put(gm.id,gm);
                                    //If Parent is  not a VGE Account add its parent id to parentid set for next fetch and update treemap
                                    if((!gm.is_vge_account__c) && (gm.parentID!=null))
                                        {   
                                            parentIdSet.add(gm.parentID);
                                            treemap.put(i,gm.parentid);                                                                                 
                                        }
                                }//end of checking whether querymap returned record
                        }//end of traversing through keymap
                }//End of querymap empty checking
        }while((!querymap.isempty()) && Limits.getQueries()<50);//Making sure SOQL Limit of 100 is not hit

//Updating incoming Accounts
    for (integer n=0; n< TriggerList.size(); n++) 
        {
            //Check if the account has a parent and has an entry in treemap,if so update the Global
            //Ultimate value to the value in Treemap.For accounts without parent ,Global Ultimate remains null
            if(Treemap.containskey(n))
                {
                    Triggerlist[n].Global_Ultimate__c = Treemap.get(n);                                                             
                }
            //Check if  Customer Account and whether without a Global Ultimate
            if(GEN_Utilities.recordType(Triggerlist[n].recordTypeId) == 'Customer Account' && (Triggerlist[n].Global_Ultimate__c!=null) )   
                {
                    //Get the record from parentRecmap
                    gm = parentRec.get(Triggerlist[n].Global_Ultimate__c);  
                    //Update the account with Global ultimate's segment and public sector               
                    Triggerlist[n].Segment__c = gm.Segment__c;
                    Triggerlist[n].Public_sector__c = gm.Public_sector__c;
                    }
        }//End of updating incoming accounts loop      
}//End of method
}//End of Class
\$\endgroup\$

2 Answers 2

2
\$\begingroup\$

Nothing jumps out at me as a huge problem since you are checking the number of queries executed in the trigger's context. I do agree with the original suggestion of preferring batch Apex, though scheduling Apex from within a trigger can be dangerous. If you don't need these values updated immediately, I would suggest trying scheduled Apex (perhaps a job that runs 1-3x daily?) You could use a custom field on the object to flag it be evaluated during the batch.

I do have one concern: what happens if you hit your self-imposed 50 query limit before you finish traversing the tree? What's the fallback in that case? Potentially other triggers could activate and use at least 50 SOQL queries before even getting to your method.

\$\endgroup\$
1
\$\begingroup\$

I'd be tempted to traverse the relationships in the queries bit more. In any given query you can use up to 5 dots to go "up" and 1 subquery to go down (to a related list if you like).

So - while this looks bit crazy and would mean some restructuring - you could end up with some massive savings in the amount of queries:

SELECT Id, Name,
    Parent.Id, Parent.Name,
    Parent.Parent.Id, Parent.Parent.Name,
    Parent.Parent.Parent.Id, Parent.Parent.Parent.Name, 
    Parent.Parent.Parent.Parent.Id, Parent.Parent.Parent.Parent.Name,
    Parent.Parent.Parent.Parent.Parent.Id, Parent.Parent.Parent.Parent.Parent.Name,
    Parent.Parent.Parent.Parent.Parent.ParentId /* Can't go further, needs new query */
FROM Account
WHERE ParentId != null

You would still have to take uniqueness into account (if 2 records with same parent will be processed with your trigger, that parent will appear once for each of them), so it still needs something like Map<Id, Account>.

And a small variant if you need something that lists all "level 3 parents" for example:

SELECT Parent.Parent.Parent.Id, Parent.Parent.Parent.Name
FROM Account
WHERE Parent.Parent.Parent.Id != null
GROUP BY Parent.Parent.Parent.Id, Parent.Parent.Parent.Name

(without GROUP BY the list will contain duplicates)

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.