+ Start a Discussion
mcrosbymcrosby 

Query for retrieving entire hierarchy?

I am trying to come up with a way to retrieve all Accounts within a hierarchy based on a supplied Id.  So, if the Id of the parent account is provided, all children records plus the parent record should be returned.  Similarly, if an Id of a child record is supplied, all parent, sibling, and children records should be returned.

Is there an easy way to accomplish this without sending multiple SOQL statements?
jf317820jf317820
Select Id, Name From Account where ParentId = {!Account.ParentId}
mcrosbymcrosby
Won't that just return the information for the immediate parent of the specified record?  I need a way to get a collection of all Accounts in a hierarchy in one SOQL if it's possible.
jf317820jf317820
ah i see, my fault. I didnt realize it was a parent of a parent situation...i believe what you're referencing involves multiple soql queries.
mcrosbymcrosby
That was my suspicion. I appreciate you trying to take a stab at it though.
MyGodItsColdMyGodItsCold

What if you had the following:

objHierarchy [ID, Name] - custom object, Name could be something like 'Ben Franklin Five and Dime' - with one corp hqtrs, branch offices, stores - each are Accounts in your hierarchy.

objHierarchyMembers [ID,Name,objHirearchy(lookup),Account(lookup), ParentID] - Accounts in a hierarchy.

Account(ID, Name, .... , objHierarchy(lookup))

So, given an account, you know the ID of the objHierarchy, so you can run a query

select Account__r.name, ... from objHirearchyMembers where objHierarchy__r.id = :account.objHierarchy__r.id

Now, the trick is to maintain the objHirearchyMembers object. This should be easily done with triggers, but the kicker is finding the objHierarchy and knowing when to create a new objHirearchy on the fly - that's what I envision objHierarchyMembers.ParentID is for (when you create an Account that has a ParentID and that ParentID has no hits in objHirearchMembers - is that when you create a new hierarchy on the fly?).

Also, 'pruning' would be a sticky wicket. Suppose the company sells off the western branch office & all the stores under it. There would be a new hirearchy created and the objHierarchyMembers would have to be targeted to isolate the structure, then a different or new objHierarchy.ID would have to be assigned.

It sounds like the kind of thing you'd want to work through manually with about 7 accounts first, then try it in a sandbox.

I've got a similar (maybe even more complicated) situation that I'm prototyping in Visual Force & it's awesome (Visual Force, ... though my scheme isn't too bad either ;)

mcrosbymcrosby

Thanks for the detailed description.  I'll file this away for future reference. 

What I ended up doing was implementing an S-Control that loops through the ParentId of the Account record until it reaches null to find the top of the hierarchy, and then got the information I needed from the main/parent Account:

var currentRecordId = '{!Account.Id}';
var parentRecordId = '{!Account.Id}';

var continueLoop = true;

while(continueLoop)

    var SearchString = "Select ParentId, Name From Account Where Id = '" + parentRecordId + "'";
    var queryResult = sforce.connection.query(SearchString);
    var records = queryResult.getArray('records');
    if(records.length == 0)
    {
        continueLoop = false;  //no records returned
        continue;
    }
    else if(records[0].ParentId == null)  //at the top of the hierarchy, get the Name
    {
        parentRecordName = records[0].Name;
        continueLoop=false;
        continue;
    }

    var account = records[0];
    parentRecordId = account.ParentId;
}

 

It works for what I need it to do.

mtbclimbermtbclimber
Since this is the "Apex Code Development" board, may I suggest that you move these queries into a custom apex webservice method that allows you to get the information from salesforce in one round trip rather than n?

Another way to accomplish this, again with Apex, is to create a custom lookup back to account called "ultimate parent" that is set on each record as it is created based on this value from the related parent object.