Salesforce / SOQL - given child, how to return the parent account of the "top level"?

Question

I have a problem when I need to find a parent top level account for a child. The best I could do was filter out from baby w /

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

Then iterate until you find parent.id zero indicating the top level account. Not very elegant and does not guarantee a "top level" account, I would prefer something like

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

But this does not work, as salesforce does not seem to allow you to go through parent-child relationships from account to account. Anyone have any suggestions?

Answer 1

You are right - there is no way to do this in a single SOQL query. Please note that your second, desired request will not give you the correct results (it will return only a record if the child account of the nearest parent did not have a parent).

It’s best to do what you said in your first block of code. You can cross relationships up to 5 objects in depth, so you can include parent.parent.parent.parent.parent.id in your SOQL choice. Iterating over fields - if none of them is null, then it issues a second SOQL query that changes CHILD_ID to parent.parent.parent.parent.parent.id. Thus, you are guaranteed to be able to find a parent without a parent (since salesforce does not guarantee any cycles).

You could make it more elegant by only ever selecting parent.id in the request and making more individual requests, but this will lead you to the API / Governor limitations, so this is probably not a great idea.

If you could use a custom object instead of an account, you could do something like a second request, since you could move from a parent-child relationship to this custom object to yourself, but I'm still not sure There is one request that will give you what you want.

Answer 2

I have come across this many times on customer sites. The best solution I have found is a custom field called "Final Parent" or sometimes called "Legal Entity", but basically the same thing. We used triggers to bypass.

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

We usually customize the field as a search to make queries like yours a lot easier. It also allows you to do very interesting data visualization. For example, using the parent search and the final search fields of the parent, you can find accounts that are siblings, cousins, stopwatch, etc.