+ Start a Discussion
Miguel BayónMiguel Bayón 

How to get all childs from a Territory2 record?

I need to show a picklist in a visualforce with all the related territories of an user. These territories are the user's assigned territories and all the territories under them in the territory hierarchy. For example, with a hierarchy like this one:
Europe
 |- France
 |- Spain <--
   |- Madrid
   |- Catalunya
     |- Barcelona
 |- Italy
A user assigned to Spain should see Madrid, Catalunya and Barcelona as possible options in the picklist. Right now I achieve this using a recursive function, but in a production org with around 1300  territories, I'm hitting CPU and SOQL limits all the time because I need to execute a SOQL query for every sub territory.

Reading Enterprise Territory Management documentation I cannot find any function provided by Salesforce to achieve this and the object itself doesn't have a named relationship between the parent a its sons that could help me reduce the number of queries. I was looking something like this:
SELECT Id, Name, (SELECT Id, Name FROM ChildTerritory2s) FROM Territory2 WHERE DeveloperName = 'Spain'
Hope someone have some workaround for achieving this without using something as demanding as a recursive function that when it hits the limits doesn't show the end of the list. Thanks.
Best Answer chosen by Miguel Bayón
Alain CabonAlain Cabon
You can reach six levels, if you use ParentTerritory2Id instead of ParentTerritory2.Name for the last one.
SELECT Id, Name,ParentTerritory2Id , ParentTerritory2.name FROM Territory2 
where ParentTerritory2.Name = 'Spain' 
or ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2Id ='0MI1v000000DMbXGAW'
or Name = 'Spain'

That is the limit ( six levels with five parents including one with an Id instead of Name).

But the resolution of the last level needs an extra read for getting the value of the Id of just the territory "Spain" itself.

So the maximum with this technique is six levels with just two queries.
 

All Answers

Alain CabonAlain Cabon
In fact, there is only one workaround to the recursive function : it is to write all the relations in extenso like below.
SELECT Id, Name,ParentTerritory2Id , ParentTerritory2.name FROM Territory2 
where ParentTerritory2Id = '0MI1v000000DMbXGAW' 
or ParentTerritory2.ParentTerritory2Id ='0MI1v000000DMbXGAW'
or ParentTerritory2.ParentTerritory2.ParentTerritory2Id ='0MI1v000000DMbXGAW'
or Name = 'Spain'

User-added image


User-added image

It is a common technique and that works because even if ParentTerritory2.ParentTerritory2.ParentTerritory2Id doesn't exist, the query still works (it is just not resolved, without error).
 
Alain CabonAlain Cabon

In each specified relationship, no more than five levels can be specified in a child-to-parent relationship.

For example, Contact.Account.Owner.FirstName (three levels) or ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain' (three levels)

It is the main blocking problem with all the relationships written in extenso.

You cannot go beyond the following query with this technique so it is also restricted but that could be sufficient for a workaround.
SELECT Id, Name,ParentTerritory2Id , ParentTerritory2.name FROM Territory2 
where ParentTerritory2.Name = 'Spain' 
or ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or Name = 'Spain'
Alain CabonAlain Cabon
You can reach six levels, if you use ParentTerritory2Id instead of ParentTerritory2.Name for the last one.
SELECT Id, Name,ParentTerritory2Id , ParentTerritory2.name FROM Territory2 
where ParentTerritory2.Name = 'Spain' 
or ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.Name ='Spain'
or ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2.ParentTerritory2Id ='0MI1v000000DMbXGAW'
or Name = 'Spain'

That is the limit ( six levels with five parents including one with an Id instead of Name).

But the resolution of the last level needs an extra read for getting the value of the Id of just the territory "Spain" itself.

So the maximum with this technique is six levels with just two queries.
 
This was selected as the best answer
Alain CabonAlain Cabon
Salesforce (open source project, rare and always very interesting)

https://github.com/SalesforceFoundation/Volunteers-for-Salesforce/blob/master/src/classes/VOL_SharedCode.cls
// This massively nested SOQL where statement looks hard coded and dumb, but as it turns out
    // there's a limit on number of campaign hierarchy levels anyway, so this isn't as dumb as it
    // seems. This method will get all campaigns in hierarchy, and keeps the logic in a single query

    /*******************************************************************************************************
    * @description Static method that takes an Id
    * Return a list of Campaign Ids that are children/grand-children &c of the given Campaign.
    * @param Id for any campaign
    * @return List<Id> of child campaigns
    ********************************************************************************************************/
    global static List<Id> listIdsCampaignsInHierarchy(Id campaignId) {
        Map<Id,Campaign> campaignsInHierarchy = new Map<Id,Campaign>(
            [SELECT Id,Name FROM Campaign WHERE IsActive =:true
             AND RecordTypeId =: recordtypeIdVolunteersCampaign AND
            (Id =: campaignId
             OR ParentId =: campaignId
             OR Parent.ParentId =: campaignId
             OR Parent.Parent.ParentId =: campaignId
             OR Parent.Parent.Parent.ParentId =: campaignId
             OR Parent.Parent.Parent.Parent.ParentId =: campaignId)]
        );
        return new List<Id>(campaignsInHierarchy.ke

"This massively nested SOQL where statement looks hard coded and dumb" (indeed) ... but that is inevitable for the best compromise (fast response, few requests). 

The alternative (keeping the same fields) as you already tested with a recursive function even optimized will be restricted by the CPU and SOQL limits all the time (blocked).

A single new field text with all the levels inside it separated with a space for using LIKE 'SPAIN% ' is difficult to maintain if you reorganize the hierarchy (thousands of possible updates).

The possible alternative is to have as many new fields in each record with each level ( LEVEL1__C, LEVEL2__C, LEVEL3__C ...) (consuming space solution but a bit simpler for reorganizing the hierarchy). 
 
That is why even the open source project of Salesforce above still use this hard coded and dumb list of relationships in extenso (restricted but sufficient for the real world).
 
Miguel BayónMiguel Bayón
Hi Alain, 
First of all, thank you so much for the answers. I've been messing around with this issue for a time and this new information it's really interesting. I applied the nested SOQL to my actual recursive function and I think it covers more than enough my needs, reducing to a sixth my current SOQL/CPU demands is as goods as it gets.
I have the same question in StackOverflow (https://salesforce.stackexchange.com/questions/306544/how-to-get-all-childs-from-a-territory2-record), so if you want some karma there, copypaste the answer from here and you got it. Thanks again for your time.
Alain CabonAlain Cabon
Hi Miguel,

It is interesting to let your question open in stackexchange even if after seven days, the chance to have an answer is now low but an unexpected smart solution could happen ( I will take a look ).

There are just really two interesting forums: salesforce.stackexchange.com and this one as you know.
The expert sfdcfox especially is very active on stackexchange. He was also a big contributor here in the past. 

Your question is one of the most dreadful problem of Salesforce. We are blocked technically at some point whatever the side you want to examinate it. Difficult to be really "sure" at the beginning.

There is this particular problem of recursive requests and the missing "SPLIT" and "REGEXP" functions for formula fields that make the "real world" coding over-complicated or limited with Salesforce. The batches have also their problems difficult to solve (... splitting the data in small independant parts (scope) is not ideal for the consistency of the results).

Unfortunately, for some merciless governor limits, we have to wait an improvment of the language or SOQL itself for new solutions of these blocking problems.

Deseo una buena continuación.