function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
bharath kumar 52bharath kumar 52 

How to access parent above 5 levels in a SOQL query?

Hi All,

How can i access a parent record more than 5 levels above in a SOQL query?

Thanks and Regards,

Bharath Kumar M

UC InnovationUC Innovation
It is not possible to access parent records more than 5 levels up. The following documentation (https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm) states that 

'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).'

This is in the first table under 'Reltionship Queries'

Hope this helps!

AM
UC InnovationUC Innovation
Please mark best answer if this helped solve your issue.
manoj bhambere 15manoj bhambere 15
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).

In each specified relationship, only one level of parent-to-child relationship can be specified in a query.
 
Gonapati Bhavitha 15Gonapati Bhavitha 15
Hi Bharath Kumar,

This is parent-child Query upto 4 levels

public List<Account> grtaccounts(){
List<Account> lstacc = [Select Id,Name,(Select Id,LastName From Contacts),(Select Id,CloseDate,Amount,StageName,Name from Opportunities),(Select Id,Status,Origin,AccountId,ContactId from Cases) From Account];
return lstacc;
}

Thanks!
Hope this helps! Please Mark It As a Best Answe
Andrew HoveyAndrew Hovey
If you want to access more than 5 levels up you'll need to make additional queries.
Take a hierarchy use case for example.
  
List<MyObj__c> nodes = [
    SELECT Id, Name, 
        Parent__c, 
        Parent__r.Parent__c, 
        Parent__r.Parent__r.Parent__c,
        Parent__r.Parent__r.Parent__r.Parent__c,
        Parent__r.Parent__r.Parent__r.Parent__r.Parent__c
    FROM MyObj__c
    WHERE Id IN :someListOfIds
];

// Identify records with higher parents
Set<Id> nodeIdsWithHigherParents = new Set<Id>();
for(MyObj__c node: nodes) {
    // safe navigation is not required when accessing queried paths, but helpful to implicitly indicate that we don’t know if they are null
    Id highestQueriedParentId = node.Parent__r?.Parent__r?.Parent__r?.Parent__r?.Parent__c;
    if(highestQueriedParentId != null) {
        nodeIdsWithHigherParents.add(highestQueriedParentId);
    }
}

From there you can use nodeIdsWithHigherParents in the WHERE clause of another query to get 5 more levels.
You'll need to write some logic to combine the two levels depending on your needs.
Phil WPhil W
A very late response on this one:

This is only possible if you are able to create appropriate cross-object formula fields that you use in your query.

I could, for example, create these two cross-object formula fields in order to access the name of great-great-great-great-great-great-great-grandparent Account for an account:
  1. Field: "G-G-G Grandparent Name":
    1. API: G_G_G_Grandparent_Name__c
    2. Type: Formula (Text)
    3. Formula: Parent.Parent.Parent.Parent.Parent.Name
  2. Field: "G-G-G-G-G-G-G Grandparent Name":
    1. API: G_G_G_G_G_G_G_Grandparent_Name__c
    2. Type: Formula (Text)
    3. Formula: Parent.Parent.Parent.Parent.G_G_G_Grandparent_Name__c
This second field effectively allows me to query "Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name" if I query it for a given Account. Deeper paths are also possible, I believe, with such chaining.