Account SOQL traverse

Question

I have a usecase,were i need to traverse account hierarchy

say i got an Account A,then i need to check whether it has a parent id, if it has a parent id--then need to get its grandparent and till the highest level.

of these parents,when i first hit a particular parent(say whose status='special') then i need to retrieve some field of that particular parent and update my record.

Since heirarchy can be upto many levels,is there an efficient way to do this?so that soqls are efficient?

this will be implemented by a trigger

Answer 1

Is there a limit to how many levels your hierarchy goes to? This one query should capture all the data you need, up to 5 levels of parent Accounts:

set<Id> parentIds = new set<Id>();
for (Account a : Trigger.new) parentIds.add(a.ParentId);

map<Id, Account> parentaccountswithparentsmap = new map<Id, Account>([select Id, Status__c, Field_to_Copy__c,
    ParentId, Parent.Status__c, Parent.Field_to_Copy__c,
    Parent.ParentId, Parent.Parent.Status__c, Parent.Parent.Field_to_Copy__c,
    Parent.Parent.ParentId, Parent.Parent.Parent.Status__c, Parent.Parent.Parent.Field_to_Copy__c,
    Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.Status__c, Parent.Parent.Parent.Parent.Field_to_Copy__c
    from Account
    where Id in :parentIds]);

Then you just need to check all the levels for each of the triggered Accounts:

for (Account a : Trigger.new)
{
    Account ParentAccount = parentaccountswithparentsmap.get(a.ParentId);
    if (ParentAccount.Status__c == 'Special')
        a.Field_to_Copy__c = ParentAccount.Parent.Field_to_Copy__c;
    else if (ParentAccount.Parent.Status__c == 'Special')
        a.Field_to_Copy__c = ParentAccount.Parent.Field_to_Copy__c;
    else if (ParentAccount.Parent.Parent.Status__c == 'Special')
        a.Field_to_Copy__c = ParentAccount.Parent.Parent.Field_to_Copy__c;
    else if (ParentAccount.Parent.Parent.Parent.Status__c == 'Special')
        a.Field_to_Copy__c = ParentAccount.Parent.Parent.Parent.Field_to_Copy__c;
    else if (ParentAccount.Parent.Parent.Parent.Parent.Status__c == 'Special')
        a.Field_to_Copy__c = ParentAccount.Parent.Parent.Parent.Parent.Field_to_Copy__c;
}  

Answer 2

It would be difficult to do this efficiently in a Trigger. However, here are some steps that may help you.

1. Declare a Map of Ids to store the Account Ids and Parent Ids

   Map<Id, Id> TopParentIdsByAccountIds = new Map<Id, Id>();
   

2. Gather all of the Account Ids in Trigger.new

   List<Id> AccountIds = new List<Id>();
   for(Account a : Trigger.new) { AccountIds.add(a.Id); }
   

3. Query for the Id and ParentId fields of each Account

   List<Account> Accounts = [Select Id, ParentId From Account Where Id IN :AccountIds];
   

4. Loop through each Account returned and add the Account Id and Parent Id to a Map of Ids while ParentId != null for any of the Accounts.

   • Perform the query again (inside the loop) for all of the Parent Accounts returned.

There are a few other things you'll need to work through to get this working (#4 can be complicated), but by using this method you should be able to limit the number of queries you will need to perform (because you're querying for all of the Parent Ids for all of the Accounts in each batch). Sorry I don't have more of an example to give you.

You may also want to check out (and install) the Inline Account Hierarchy in your Developer Edition of Salesforce. It's not programmed to efficiently get the Top-of-Hierarchy Account, but it has a more detailed example.

Answer 3

Create an ultimateParentId on each account. This will have the AccountId for the highest level account. Update this field through the trigger and run a soql to fetch all accounts where ultimateparentId = "Id".