+ Start a Discussion
Philip_FPhilip_F 

Overcoming Governor Limitations and Recursive Parent-Child Relationship SOQL

Hello All,

 

I'm working on a solution in a trigger where I need to access the entire hierarchy of parent accounts for a given account.  At the moment, I'm recursively querying the account table for each account's Parent based on ParentId.  

 

This solution is fragile because if there were enough parent account to child account layers, then the trigger would hit the query limit.

 

Is there a way to access data about accounts at each level in a hierarchy without recursive querying?  It would be splendid if I could run a single query and get back a Map of Accounts or something along those lines.

  


private static string getTopParentAccountName(Account thisAccount) {
if (thisAccount.ParentId != null) {
// Get the parent account for this account and pass it to this function for recursion
Account parentAccount = [Select a.ParentId, a.Name, a.Id From Account a where a.Id = :thisAccount.ParentId];
return getTopParentAccount(parentAccount);
}
else { // There's no parent account
return thisAccount.Name;
}
}

 

The code above is representative of the logic I'm employing.  (This isn't the actual code I'm using, it's something I worked up to illustrate my point, so please ignore any minor errors in the code.) 

 

I checked the Apex Relationship Queries Documentation and found the following comments that seem relevant:
  • In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName (three levels).
  • In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specified Account, the SELECT clause could only specify the Contact or other objects at that level. It could not specify a child object of Contact. 
 
Any thoughts on how to handle this situation and get around the governor limitations?     
 
All input is appreciated!
 
Cheers,
-Philip
TSH53TSH53

One idea you could use is to store/cache 'generations' of account records in a list of maps. Then, when you iterate the Accounts from the trigger list you perform the lookup using the maps rather than using database queries. Using this method, the maximum number of queries should be limited to the longest leg in the account hierarchy - for example, the longest hierarchy for an account is A>AB>ABC, then you should have a max of 3 queries.

 

Below is some code I created to illustrate the approach. It compiles, but I did not test it for complete accuracy, so I apologize if there are any errors. It should be good enough to get you started. Let me know how it turns out.

 

Terry Hamer

InSitu Software

 

// Create a list of maps - each entry represents a 'generation' of parents in the parent/child hierarchy

// (account, parent, grandparent, great-grandparent, etc.) List<Map<Id, Account>> listAccountMaps = new List<Map<Id, Account>>(); // Start with the accounts from the trigger list - put them in a map, indexed by Id. Map<ID, Account> mapAccountsToQuery = new Map<ID, Account>((List<Account>)Trigger.new); // Create a list of parent ids - used for single query to receive parent generation accounts. List<ID> listParentIDs = createListOfParentIDsFromAccountMap(mapAccountsToQuery); while (listParentIDs.size() > 0) { // Use a single database query to get the parent account records, put results in a map, and add the map to the list. // FYI - need to set appropriate Select Fields. Map<ID, Account> mapParentResults = new Map<ID, Account>([SELECT ID, ParentID FROM Account WHERE Id IN :listParentIDs]); listAccountMaps.add(mapParentResults); // Create a list of Parent ids using the results from the previous query. listParentIDs = createListOfParentIDsFromAccountMap(mapParentResults); } // Now iterate the trigger.new list and perform parent look-ups via the list of generational Account maps. for (Integer i = 0; i<Trigger.new.size(); i++) { Account acctUltimateParent = (Account)Trigger.new[i]; ID idParent = acctUltimateParent.ParentID; // Iterate the list of maps until you find a null parent id. for (Integer j=0; (idParent != null && j<listAccountMaps.size()-1); j++) { acctUltimateParent = listAccountMaps[j].get(idParent); idParent = acctUltimateParent.ParentID; } // Right here, acctUltimateParent should hold the ultimate parent for Trigger.new[i] - do whatever needs to be done. } private List<ID> createListOfParentIDsFromAccountMap(Map<ID, Account> mapAccounts) { List<ID> listParentIDs = new List<ID>(); // Iterate map to create list of parent ids. for (Account acct : mapAccounts.Values()) { listParentIDs.add(acct.ParentID); } return listParentIDs; }

 

Philip_FPhilip_F

Hi Terry,

 

Thanks for the input.  Yes, that's a good suggestion to minimize the number of queries to be the number of tiers in the hierarchy (minus one because the initial child account has the parent data for free) by using the loop to build up the family tree in a list of maps.

 

I'm wondering if there is a way to avoid looping altogether in the circumstance that the number of parent-child relationships is too great (e.g. A>B>C>...>X>Y>Z>AA>BB>CC> etc...).

 

I have the impression that there's no way to avoid the looping, but perhaps there's a guru who has the answer to this situation.

 

Cheers,

-Philip

 

 

tstrongtstrong

This code is exactly what I've been looking for to implement a trigger I need when a Contact is being changed to another Account outside of the existing Account's hierarchy.

 

Would you mind giving me some input on how you can modify this to evaluate if the ultimate Parent the Contact.Account is changed before update?

 

Thanks so much for any help you can provide!