[SalesForce] Dynamic SOQL, Access Subquery Results

I'm having trouble understanding how to access each individual result returned from a subquery. The picture shows the code which:

  1. takes fields and objects chosen by a user and assigns them to String variables,
  2. the string vars are used to query a dynamic query string,
  3. the query string is feed into a database.query and the result is stored in an array
  4. I can individually access each of the query results from this array (parent object results),
  5. But I have not been able to individually access the subquery results.

If I feed the subquery results into an array…something like:

SObject[] c = p.get(i).getSObjects(childO);

I still can't get to each individual child record per parent. I can only see all child records per each parent. I need to get to each child record per adult, so that I can roll up results.

Can anyone help?

// These strings will be populated dynamically from a VF page
String parentO  = 'Account';
String parentF  = 'Products_Purchased__c';
String childO   = 'Opportunity';
    if(childO  == 'Opportunity'){ childO = 'Opportunities'; }
String childF   = 'Product_Type__c';

// create dynamic SOQL query string
String query = 'SELECT id, (select ' +childF+ ' from ' +childO+') FROM ' +parentO;

// query and store 33 results in variable p
SObject[] p = Database.query(query);

for(Integer i = 0; i < p.size(); i++){
    system.debug('Parent result from SOQL query: ' +p[i]);
    if(p.get(i).getSObjects(childO) <> null){
        system.debug('Child result(s) from SOQL query: ' +p.get(i).getSObjects(childO));
        system.debug('Child field: ' +c.get(i).get(childF));
    }
}

enter image description here

Best Answer

You can store it as a List<SObject> and then iterate over that collection.

List<SObject> children = parent.getSObjects('Opportunities');
for (SObject child : children)
{
    system.debug(child);
}

Also, if you want to roll up data from Opportunity to Account, you can just use a Rollup Summary Field.

If you want to do fancy aggregations that aren't supported out of the box, the Declarative Lookup Rollup Summaries tool almost certainly does everything you need without requiring you to write a single line of code.

Related Topic