[SalesForce] SOQL query is unable to get (parent(Child (Child of child)))

Parent to child SOQL query, Unable to fetch data, giving this ERROR

SOQL statements cannot query aggregate relationships more than 1 level
away from the root entity object

when I am querying following Custom objects.Tower__c(Parent),Floor__c(Child),Unit__c(Child Of Floor).

Please suggest me some solution for it.

SELECT Floor_Number__c, (SELECT name FROM Units__r) FROM Floors__r) FROM Tower__c

Best Answer

The error message describes the issue pretty well.

The "root entity" is the object that you're using in the FROM clause of the outermost query (Tower__c in your case), and each time you query from a parent record to a child record (or from a child record to a parent record), that counts as one "level" away.

You can only query one level deep from parent -> child.
Tower__c -> Floor__c is one parent -> child level
Tower__c -> Floor__c -> Unit__c is two parent -> child levels (specifically, Unit__c is two levels away from Tower__c, and is thus invalid in a query starting from Tower__c).

Your options here are to split this into two separate queries, or to adjust your query to start from Floor__c instead of Tower__c.

Splitting into two queries:

// The first query only gathers child records one level deep (Floors).
// Storing this in a map will help out with the second query.
Map<Id, Tower__c> towersMap = new Map<Id, Tower__c>([SELECT Id, (SELECT Id, Floor_Number__c FROM Floors__r) FROM Tower__c WHERE <some filters here are probably a good idea>]);

// The second query starts from Floor__c, and queries one child level down (to Unit__c).
// Yes, the first query already gathered all of the Floor__c records (and 
//   therefore, this query 'wastes' some of the 50,000 query row limit), but the
//   end result is convenient to use if you can bear to use this.
// Filtering this query on the Tower__c records previously queried (which is the main 
//   reason why I stuck the results of the first query into that map) ensures that
//   we don't query any extra Floor__c records.
// Placing these results into a map as well makes life easier down the road.
Map<Id, Floor__c> floorsMap = new Map<Id, Floor__c>([SELECT Id, (SELECT Name FROM Units__r) FROM Floor__c WHERE Tower__c IN :towersMap.keySet()]);

// Using the query results
for(Tower__c tower :towersMap.values()){
    // Getting to the floors of each tower is the same as usual
    for(Floor__c floor :tower.floors__r){
        // When we want to access the units on each floor, we need to switch to using
        //   the map generated by the second query
        for(Unit__c unit : floorsMap.get(floor.Id).Units__r){
        }
    }
}

Changing the root object of your query:

// Instead of starting from Tower__c, we can start from Floor__c.
// It's simple enough to query up to retrieve parent fields (from Tower__c), though
//   this method would repeatedly fetch the data from Tower__c multiple times (taking
//   up more heap space, though the heap size limit is generally less of an issue than
//   the query and query rows limits).
List<Floor__c> floors = [SELECT Id, Tower__c, (SELECT Name FROM Units__r) FROM Floor__c WHERE <some filters here would be a good idea>];

// The big downside to this approach is that if you need to perform operations on individual
//   towers, you'll need to do some manual work to associate each floor to its respective
//   tower.
Map<Id, List<Floor__c>> towerIdToFloors = new Map<Id, List<Floor>>();
for(Floor__c floor :floors){
    // This is just my preferred method to populating maps where the value is another collection.
    if(!towerIdToFloors.containsKey(floor.Tower__c)){
        towerIdToFloors.put(floor.Tower__c, new List<Floor__c>());
    }

    towerIdToFloors.get(floor.Tower__c).add(floor);
}

// Using this one is a bit different than the two query example
for(Id towerId :towerIdToFloors.keySet()){
    for(Floor__c floor :towerIdToFloors.get(towerId)){
        for(Unit__c unit :floor.Units__r){
        }
    }
}

// If you are querying (and later using) more than just the Id field on Tower__c, it
//   may be useful to build a map like this instead.
// This relies on knowing that <relationship field name>__r is a full SObject instance
//   when going from child -> parent (e.g. someFloor.Tower__r), and a List of
//   SObjects when going from parent -> child (e.g. Tower.Units__r)
// If you do go with this block, using it is simply looping over towersMap.values(),
//   then tower.Floors__r, and finally floor.Units__r
/*
Map<Id, Tower__c> towersMap = new Map<Id, Tower__c>();
Tower__c tempTower;
for(Floor__c floor :floors){
    if(!towersMap.containsKey(floor.Tower__c)){
        tempTower = floor.Tower__r;
        towersMap.put(tempTower.Id, tempTower);

        if(tempTower.Floors__r == null){
            tempTower.Floors__r = new List<Floor__c>();
        }
    }

    towersMap.get(floor.Tower__c).Floors__r.add(floor);

}
*/
Related Topic