Accessing grandparent through SOQL

Question

I'm trying to access a grandparent record through a SOQL query and am having some trouble. From my understanding, you can go 5 levels deep with a child-to-parent relationship in a query, so I don't know why this isn't working.

For this example, child__c has a lookup to parent__c and parent__c has a lookup to grandparent__c.

Child__c dbRecord = [
    SELECT
        Id,
        Parent__r.Name,
        Parent__r.Grandparent__c,
    FROM
        Child__c
    WHERE
        Id = :childId
];

what's odd is that I'm running this in the dev console and while it isn't throwing an error, the value of "Grandparent__c" is never populated as if it wasn't in the query at all.

Any ideas or am I misunderstanding the SOQL child-to-parent relationship somehow?

Answer 1

Please include parent__r.grandparent__r.name in your query and try executing it , you might encounter the issue that I did as below

well , i did some research and your understanding of 5 levels deep is right , and i however created a relationship in my org and traversed through child to parent i.e (Book ->Question ->Test) and when i performed the below query , i did get the result but as in the format [object Object]

select name,question__r.name,question__r.test__r.name from book__c

there is however a post regarding this as copied below , so you might try using anonymous apex and execute the code. i have tried executing the below query per my org and was able to traverse child to parent as per the mentioned post below

Child-to-Parent Query - [object Object] is the result

//query that worked in anonymous apex

list<book__c> result =[select name,question__r.name,question__r.test__r.name from book__c];

system.debug(result[1]);

Answer 2

It would be normal to use the same relationship field for the parent to grandparent as the child to parent to allow the depth of the relationships to be open ended.

So for example:

Child__c c = [
    SELECT
        Name,
        Parent__r.Name,
        Parent__r.Parent__r.Name,
    FROM
        Child__c
    WHERE
        Id = :childId
];

String childName = c.Name;
String parentName = c.Parent__r ! null
        ? c.Parent__r.Name
        : null;
String grandParentName = c.Parent__r ! null != null && c.Parent__r.Parent__r != null
        ? c.Parent__r.Parent__r.Name
        : null;

However, you could add additional relationship fields instead:

Child__c c = [
    SELECT
        Name,
        Parent__r.Name,
        GrandparentParent__r.Name,
    FROM
        Child__c
    WHERE
        Id = :childId
];

String childName = c.Name;
String parentName = c.Parent__r != null ? c.Parent__r.Name : null;
String grandParentName = c.GrandParent__r != null ? c.GrandParent__r.Name : null;

or if you only want the ID values (the foreign keys):

Child__c c = [
    SELECT
        Id,
        Parent__c
        GrandparentParent__c,
    FROM
        Child__c
    WHERE
        Id = :childId
];

Id childId = c.Id;
Id parentId = c.Parent__c;
Id grandParentId = c.GrandParent__c;