0

The requirement is to store object name and field name in the custom object so user can defined object and condition to display survey question.

I am developing skeleton code to support the requirement and now I am stuck with nested-loop while trying to run dynamic query. My code is like below :

//query to retrieve the question with object name and condition
List<Question_Condition__c >  listCond = [select  Object__c, Object_Field__c, Index__c, Operator__c, Value__c from Question_Condition__c ]

Map<String,Set<String>> mapObjField = new Map<String,Set<String>>();                                                    
//to populate object and fieldname
for(Question_Condition__c c :listCond ){
    if(!mapObjField.containsKey(c.Object__c){

        mapObjField.put(c.Object__c,new Set<String>{c.Object_Field__c});

    }
    else {

        Set<String> setFld =mapObjField.get(c.Object__c);
        setFld.add(c.Object_Field__c);
    }

}   
system.debug('mapObjField ::'+ mapObjField) ;   
//sample output as below

// mapObjField ::{Account={RecordTypeId}, Opportunity={StageName, Type}}


List<String> listSql =new List<String>();
//generate the query based on object and field name retrieve
for (String x : mapObjField.keySet()){

    String soql = 'select '+ mapObjField.get(x) + ' from ' + x ;
    listSql.add(soql);

}

//run the query
for(String soql : listSql){

     SObject obj = Database.query(soql); //this will run inside this loop

}

How I can get away from nested loop in this part .Any better design pattern?

for(String soql : listSql){

         SObject obj = Database.query(soql); //this will run inside this loop

    }
  • 1
    Just a quick note that you don't actually have any nested loops in the code you've provided. You are, however, running queries inside of a loop. – Derek F Aug 24 '18 at 12:55
3

You are performing SOQL queries across multiple objects that don't have any defined relationship with one another. There is no way to optimize this into a single query; in fact, this is one of the few situations in which performing a query in a loop appears to be the correct solution.

I say "appears" because the actual objective of your activity here is not clear;

SObject obj = Database.query(soql);

is not a sensical pattern for performing this type of dynamic query, or indeed any query.

While it's possible that more information might yield a better solution, overall, if your intention is to support a dynamically-defined query across N objects, without using SOSL, you should expect to perform N queries.

On the contrary, if your objective is to support multiple dynamic criteria in a query or queries against the same object (survey questions?) it's possible your design could be refined and optimized into a single query that's postprocessed in Apex. You haven't supplied enough information to be clear on this point, but the fact that you're dynamically setting the query object name suggests that no further SOQL optimization is likely to be possible.

  • Thank you for your opinion. Sometimes I get too ambitious. I need to have some filtering , controlling code somewhere to prevent it from scew up the performance. – unidha Aug 27 '18 at 2:31

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.