Salesforce/SOQL - 给定子级，如何返回“顶级”家长账户？
我有一个问题，我需要找到孩子的顶级家长帐户。我能做的最好的事情就是从子项中过滤出来，
SELECT id, name, parent.id, parent.parent.id, parent.parent.parent.id,
FROM Account WHERE id=CHILD_ID
然后进行迭代，直到找到一个空的parent.id来指示顶级帐户。不是很优雅，也不能保证“顶级”帐户，我更喜欢类似的东西，
SELECT id, name,
(SELECT id, name,
FROM Accounts WHERE id = CHILD_ID)
FROM Account WHERE parent.id = null
但这不起作用，因为 salesforce 显然不允许您在帐户之间遍历父子关系。这里有人有什么建议吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我在客户现场多次遇到过这种情况。我发现的最好的解决方案是一个名为“最终父级”或有时称为“法律实体”的自定义字段，但基本上是相同的。我们使用触发器来进行遍历。
基本思想是，每当您创建子帐户的帐户时，请将“最终父级”字段复制到子记录中。
通常，我们将该字段设置为查找，以便像您这样的查询更容易。它还允许您进行一些非常有趣的数据可视化。例如，通过使用父查找和最终父查找字段，您可以查找兄弟姐妹、表兄弟姐妹、二代表兄弟姐妹等帐户。
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.
你是对的 - 没有办法在单个 SOQL 查询中做到这一点。请注意，您的第二个所需查询也不会为您提供正确的结果（如果子帐户的直接父级没有父级，它只会返回记录）。
最好的选择是按照您在第一个代码块中所说的进行操作。您可以遍历最多 5 个对象深度的关系，因此您可以在 SOQL 选择中包含parent.parent.parent.parent.parent.id。迭代字段 - 如果它们都不为空，则发出第二个 SOQL 查询，将 CHILD_ID 更改为parent.parent.parent.parent.parent.id 的值。这样你就可以保证最终找到一个没有父母的父母（因为 salesforce 保证没有循环）。
您可以通过仅在查询中选择parent.id并执行更多单独的查询来使此操作更加优雅，但这将使您遇到API/调控器的限制，因此这可能不是一个好主意。
如果您可以使用自定义对象而不是帐户，则可以执行类似于第二个查询的操作，因为您将能够从该自定义对象到其自身遍历父子关系，但我仍然不确定是否存在单个查询即可满足您的需求。
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.