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

Question

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?

Answer 1

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.