0

I am new to SOQL and working with a 4 Level parent - child Relationship:

Level1 (Parent) Level2 (Child) Level3 (Grand Child) Level4 (Great Grand child)

I am trying to pull records from Level1, Level2 & Level4

eg:

Select Level1_r.Id, Level1_r.date, c.category,(SELECT name FROM Level_3s__r.Level4s__r) FROM Level2_c c

This is not working for me, I dont know where I went wrong. It will be great if someone have a solution for this

3
  • What error are you getting when you try this query? It should be possible to go 5 levels, but not more. In fact, there is a specific error for exceeding the relationship limits.
    – nbrown
    Sep 23, 2020 at 13:13
  • It says Level1_r is invalid. Kindly guide me on syntax
    – Amudha
    Sep 23, 2020 at 13:16
  • Well, Level1_r with only one underscore would be incorrect syntax. You need two: Level1__r.
    – nbrown
    Sep 23, 2020 at 14:41

1 Answer 1

3

What you are mentioned in a question is a parent-to-child relationship. you can go only 1 level deep. So you can't use it here

Let's say, you have Level1__c, Level2__c, Level3__c, Level4__c objects with respective lookup fields on parent object.

Option 1

If you need to get Level4__c records you will need to perform the following query, In this way, you are querying data "from child to parent"

Level4__c objects by Level1_c object id

SELECT Id, Name FROM Level4__c WHERE Level3__r.Level2__r.Level1__c  = :SOME_LEVEL1_ID 

Level3__c objects by Level1_c object id

SELECT Id, Name FROM Level3__c WHERE Level2__r.Level1__c  = :SOME_LEVEL1_ID

Level2__c objects by Level1_c object id

SELECT Id, Name FROM Level2__c WHERE Level1__c  = :SOME_LEVEL1_ID

or, with one query:

SELECT Id, Name, Level3__c, Level3__r.Name, Level3__r.Level2__c, Level3__r.Level2__r.Name FROM Level4__c WHERE Level3__r.Level2__r.Level1__c  = :SOME_LEVEL1_ID 

Option 2 Query ids from parent to child and storing it it some variable:

Id level1Id = 'SOME_ID';
List<Level2__c> level2Children = [SELECT Id, Name FROM Level2__c WHERE Level1__c = :level1Id];

List<Level3__c> level3Children = [SELECT Id, Name FROM Level3__c WHERE Level2__c IN :level2Children];

List<Level4__c> level4Children = [SELECT Id, Name FROM Level4__c WHERE Level3__c IN :level3Children];
3
  • do you mean to say that i cant achieve this in a single query?
    – Amudha
    Sep 23, 2020 at 14:03
  • @Amudha yes, please check the examples, that I provided Sep 23, 2020 at 15:34
  • Thank you @Oleksandr
    – Amudha
    Nov 18, 2020 at 10:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .