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

up vote 4 down vote accepted

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.

  • "Method does not exist or incorrect signature: void getSObjects(String) from the type List<SObject>." Not sure what I'm doing wrong, but I tried that yesterday. Same result today. – Rock Tober Sep 12 '17 at 17:23
  • The single parent would be p.get(i) in your example. You need to call it from a single SObject instance (p[i]), not the entire collection (p). – Adrian Larson Sep 12 '17 at 17:26
  • Ah, I have to use it with a nested for loop. Thanks. About the rollup field suggestion, this has to work for any object (dynamic), and perform more functionality than a standard roll-up, like be able to concatenate text fields, for instance. – Rock Tober Sep 12 '17 at 17:38
  • You should look at the Declarative Lookup Rollup Summaries tool, which almost certainly does everything you need. Why reinvent the wheel? – Adrian Larson Sep 12 '17 at 17:41
  • We have a paid solution called Rollup Helper in play right now, but it's giving our admin pain. I believe they'd be willing to use another program if there's no cost. The cost is the main issue. So, they tasked us with developing a tool in-house that's not packaged, and can therefore be worked on as needed. – Rock Tober Sep 12 '17 at 18:14

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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