2

Struggling with building a nested for-loop to parse through the following scenario :

String table = 'Account';
String subtable = 'Contacts';
String soql = 'Select id, (id from ' + subtable + ') from ' + table + ' limit 5';

List<sObject> results = Database.query(soql);

My attempts thus far at coming up with a working nested for loop to cycle through the 'result' collection have failed (without abandoning my variables and resorting to concrete tables).

SObject subTableObj = (SObject)Type.forName(subtable).newInstance();

for (sObject o : results) { // this works ok
    //for (sObject subo : o.subTableObj) {
    //for (sObject subo : o.sObject()) {
    //.. and other failed variations using Schema...getSObjectType
    //   or getSObjectTypes
    //}
} 

Thanks for any/all guidance.

1
  • 1
    can you please edit your question to show us the code that you've tried (for the nested loop portion)?
    – Derek F
    Jan 12 at 22:36
3

Given you have the child relationship name (i.e. "Contacts" in your example), my first attempt was to simply use SObject's get() method, like you would with any other field.

String query = 'SELECT Id, (SELECT Id FROM Contacts) FROM Account';
List<SObject> results = database.query(query);

for(SObject result :results){
    for(SObject child :(List<SObject>)result.get('Contacts')){
        system.debug(child);
    }
}

This, however, throws an error about "Contacts" not being a field (which to be fair, it isn't).

Looking through the documentation on the SObject class, the description for getSObjects() caught my eye.

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.

So with that in hand, it's a pretty easy modification to my first attempt to get this working.

String baseObj = 'Account';
String childObj = 'Contacts'; // must be the child relationship name
String query = String.format('SELECT Id, (SELECT Id FROM {0}) FROM {1}', new List<String>{
    childObj, baseObj
});

List<SObject> results = database.query(query);

for(SObject result :results){
    for(SObject child :result.getSObjects(childObj)){
        system.debug(child);
    }
}

The String.format() business is just a way to avoid long concatenations, and is similar to the approach in your example. I do think that using String.format() is probably easier to reason about and less error-prone.

3
  • This is absolutely fantastic - thank you! Wish I would have posted it earlier in the day, could have saved myself six hours :) Thanks Derek!
    – Pete
    Jan 13 at 12:46
  • @Pete The child loop variable in the inner loop isn't a collection, it's an SObject, so no third loop required. When working with generic SObjects (which is pretty much always going to be what you're doing when dynamic SOQL is involved), we can't use dot notation to access fields, we need to use .get(), e.g. child.get('Id');. Be aware that .get() returns an Object, and you'll need to do some type-casting to be able to do much of anything with the result.
    – Derek F
    Jan 13 at 14:09
  • Thank you again @derek-f ! I added an extra 'wrinkle' - in case you'd be so inclined to help me again :) ==> looping over dynamic soql of indeterminite dimensions salesforce.stackexchange.com/q/367652/110121?sem=2
    – Pete
    Jan 23 at 13:00

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.