How to Query all fields of grand parent record with SOQL?

Question

I have a App__c with n number of fields. Page__c is another custom object has few fields and a field App_Id__c which is Master-Detail to App__c object. Field__c is another custom object has fields and a field Page__Id__c which is Master-Detail to Page__c object.

I am querying all the Field__c object records, but the issue I am facing is When I query a record of Field__c I also need the query to get all fields of the corresponding Grand parent record App__c.

I know we can get the grand parent record's field like for eg:Name like shown in below query, but I dont want to mention each and every field in the query like this, I want to get all the field values of the grand parent record without having to write each and every field in the query.

List<Field__c> fieldRecords = [Select Id, Name, Page_Id__r.App_Id__r.Name From Field__c];

Answer 1

There's no way around it, you will need to write out Page_Id__r.App_Id__r.<field> for every field you want from your grandparent record.

The best you'd be able to do is potentially save some typing by breaking out the query for granpdarent records into a separate query. You'll need to be the one to decide if it's worth it to expend an extra query and deal with needing to pull the grandparent record from a map every time you need to access it.

Answer 2

Dynamic SOQL and a little bit of DescribeSObjectResult magic should work for you.

This is simple anon apex and I advise you to break it out into its own method that could accept many queryObject and many type of relationshipAPI.

String relationshipAPI = 'Parent__r.GrandParent__r.';
String queryObject = 'Child__c';

String[] fieldNames = new String[] {};
DescribeSObjectResult describeResult = GrandParent__c.SObjectType.getDescribe();
Map<String, Schema.SObjectField> fieldMap = describeResult.fields.getMap();

for (String fieldName : fieldMap.keySet()) {
    fieldNames.add(relationshipAPI + fieldName);
}

String soql = ''
    + 'select'
    + ' Id'
    + ', ' + String.join(fieldNames, ', ')
    + ' from ' + queryObject;

Database.query(soql);

The idea here is that using the relationshipAPI to traverse from child through to grandparent, a describe operation obtains the tokens needed to access the API names of the grandparent fields. This is then built into a String using the join() method from String class and later used in a dynamic SOQL.

Note, you will need to consider some null checking and field that are not "queryable" such as those when Person Accounts are enabled.

Answer 3

I had a look at FIELDS(ALL) but it unfortunately doesn't look like you can use it for traversing relationship fields like this.

Your other option appears to be doing something like this to build a SOQL string, but formatting it so that the fields are coming off the parent/grandparent records.

EDIT: See TSmith's answer for some example code; they were kind enough to go in-depth.