CODEWITHSUNDEEP
salesforceapex-codesoql
Dave S
Dave S

Reputation: 609

Salesforce/SOQL - Given child, how to return "top level" parent account?

I have an issue where I need to find the top level parent account for a child. The best I've been able to do is filter up from the child w/ 

 SELECT id, name, parent.id, parent.parent.id, parent.parent.parent.id,
 FROM Account WHERE id=CHILD_ID

Then iterate through until you find a null parent.id indicating top level account. Not very elegant and doesn't guarantee "top level" account, I would have preferred something like

SELECT id, name, 
    (SELECT id, name, 
     FROM Accounts WHERE id = CHILD_ID)
FROM Account WHERE parent.id = null

But that doesn't work as salesforce apparently does not let you traverse parent-child relationships from account to account. Anyone have any suggestions here?

Upvotes: 5

Views: 10381

Answers (2)

Eric Sexton
Eric Sexton

Reputation: 506

I have run into this many times at customer sites. The best solution I've found is a custom field called "Ultimate Parent" or sometimes called "Legal Entity", but basically the same thing. We used triggers to do the traversal.

The basic idea is that whenever you create an account that is a child account, you copy the "Ultimate Parent" field into the child record.

Usually, we setup the field as a lookup so that queries like yours are much easier. It also allows you to do some very interesting data visualization. For example, by using the parent lookup and the ultimate parent lookup fields, you could find accounts that are siblings, cousins, seconds cousins, etc.

Upvotes: 1

Dan K.
Dan K.

Reputation: 1532

You're right - there's no way to do this in a single SOQL query. Note that your second, desired, query wouldn't give you the correct results either (it would only return a record if the child account's immediate parent had no parent).

Your best bet is to do what you said in your first code block. You can traverse relationships up to 5 objects deep, so you can include parent.parent.parent.parent.parent.id in your SOQL select. Iterate through the fields - if none of them are null, then issue a second SOQL query that changes CHILD_ID to parent.parent.parent.parent.parent.id's value. That way you're guaranteed to eventually find a parent with no parent (since salesforce guarantees no cycles).

You could make this more elegant by only ever selecting parent.id in the query and doing more individual queries, but that will run you up against API/governor limits, so it's probably not a great idea.

If you could use a custom object instead of Account, you could do something like the second query, since you would be able to traverse the parent-to-child relationship from that custom object to itself, but I'm still not sure there's a single query that will give you what you want.

Upvotes: 5

Related Questions