2

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?

2
  • How about String Id = root.Account__r.Id; – Sergej Utko Apr 30 '13 at 13:09
  • @Sergey That would not be dynamic. – ipavlic Apr 30 '13 at 13:49
5

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.

3
  • I believe that the root.getSObject('Account__r') will actually produce an account, and will try to get an Id field from it (i.e. from an Account sObject). But that is not queried (Account is not the same as root) and will result in an System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Id exception. Am I missing something? – ipavlic Apr 30 '13 at 13:51
  • Did you try it? – jkraybill Apr 30 '13 at 14:02
  • Apparently it works. I first tried with intermediate SObjects i.e.: SObject obj = root.getSObject(...); obj.get(...) which resulted in an exception. Chaining works. – ipavlic Apr 30 '13 at 14:36
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.