How to get fields in deeper object tree levels with reflection

Question

I'm constructing a dynamic query to get some fields from the database. I use a root object for that purpose and branch out from it.

My_Custom_Object__c
   -> Account__r.Id

So if I want to get an Account Id, I'll go through my root object to the Id:

queryString = 'Select c.Account__r.Id from My_Custom_Object__c where some condition';

I can then dynamically query to get linked fields:

My_Custom_Object__c root = Database.query(queryString)

Once I get that object back, I'd like to extract the fields. In other words I would like to say something like:

String Id = root.get('Account__r.Id');

Unfortunately, it seems that get works only for the first level (fields, not fields in related objects). I though about recursively working through the tree with gets or converting the root to JSON and extracting the required fields from there. Both approaches seem cumbersome or error-prone.

What would be a good approach to extract the fields of related objects by their name from the root object?

Answer 1

It is error prone, but possible to do well.

root.getSObject('Account__r').get('Id');

aka

root.getSObject(currentLevel).get(currentField);

To figure out the gotchas and parsing/mapping strategies, look at some libraries like Java OGNL or JSP EL. They are possible to emulate and do dynamic deep querying while learning from their designs.

Wish there was some better support for doing describe-like calls on SOQL results, but sadly haven't seen any.

Answer 2

An interesting coda to @jkraybill's useful answer

Suppose you wanted to get the sizes of each of the subqueries within a dynamic SOQL expression without casting to any concrete SObject (motivated by the OP). For example:

Sobject[] sList = Database.query('select id, ' + 
                                   '(select id from Contacts), ' +
                                   '(select id from Foos__r) from Account');

for (Sobject s: sList)
   system.debug('s.get('id') + ' with Contacts:' +s.getSObjects('Contacts').size());

RESULT (V35) (a surprise)

If some account in the returned list has no Contacts, the getSobjects() method returns null, not an empty list. Hence you have to test for null to avoid an NPE.

Despite the documentation on the SObject class getObjects() method which says it returns a list of SObjects

getSObjects(fieldName)

Returns the values for the specified field. This method is primarily used with dynamic DML to access values for associated objects, such as child relationships. > Signature

public sObject[] getSObjects(String fieldName)

Parameters fieldName Type: String

Return Value Type: sObject[]

curious that the doc says it is used for Dynamic DML but the example in the doc is Dynamic SOQL. Doc feedback filed