function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Siva SakthiSiva Sakthi 

Avoid for loop method having query? Causing Too many SOQL queries: 101

Hi, 

I have some methods one method for loop calling another method, that method having query and return values. some times causing error message like Too many SOQL queries: 101 . How to avoid this, my sample code is below. Guide me to Solve this issue . Where i have to  change my code .

 public List<MarkSheetDomain> getMarksheet(){        
        List<AggregateResult> subjectCodes = new List<AggregateResult>();
        List<MarkSheet> marks = new List<MarkSheet>();
        SemesterSelection = string.escapesinglequotes(SemesterSelection);
        subjectCodes = [Select Subject_Code__c code from catalog__c where Paper__c ='Maths'  And Subject_Code__c != null GROUP BY Subject_Code__c];        
        for (AggregateResult subjectcode : subjectCodes){
            MarkSheet mark = new MarkSheet();
            mark.SubjectCode = String.valueOf(subjectcode.get('code'));        
            mark.ExternalExam = getExternalExams(mark.SubjectCode);           
            marks.add(mark);
        }
        return marks;
    }    
    public String getExternalExams(string Subjectcode) {  
        RollNo = string.escapesinglequotes(RollNo);  
        External_Exam__c ExtExm = new External_Exam__c();
        ExtExm = [Select Title__c, Score__c from External_Exam__c where Sem__c=:SemesterSelection And Subject_Code__c =:Subjectcode and Student__r.Roll_No__c =:RollNo];
        System.debug('Test on Mark' + ExtExm );
        System.debug('Test on Mark' + ExtExm.Score__c);
        return String.valueOf(ExtExm.Score__c);         
    }

Advance Thanks
Maheshwar
Deepak Kumar ShyoranDeepak Kumar Shyoran
Hi Maheshwar,

Use below code and this will solve your problem. 
  
public List<MarkSheetDomain> getMarksheet(){        
        List<AggregateResult> subjectCodes = new List<AggregateResult>();
        List<MarkSheet> marks = new List<MarkSheet>();
        SemesterSelection = string.escapesinglequotes(SemesterSelection);
        subjectCodes = [Select Subject_Code__c code from catalog__c where Paper__c ='Maths'  And Subject_Code__c != null GROUP BY Subject_Code__c];        
        List<String> subjectCodesList = new List<String>();
		
		for(catalog__c code : [Select Subject_Code__c from catalog__c where Paper__c ='Maths'  And Subject_Code__c != null ] )
				subjectCodesList.add(Subject_Code__c);
		
		Map<String,String> externalExalMap = getExternalExams(subjectCodesList);
		for (AggregateResult subjectcode : subjectCodes){
            MarkSheet mark = new MarkSheet();
            mark.SubjectCode = String.valueOf(subjectcode.get('code'));        
            subjectCodesList.add(mark.SubjectCode);
            mark.ExternalExam	= externalExalMap.get(mark.SubjectCode + RollNo );
			marks.add(mark);
        }
        return marks;
    }    
    public Map<String,String> getExternalExams(List<String> Subjectcode ) {  
        Map<String,String> externalExalMap = new Map<String,String>() ;
		for(External_Exam__c ExtExm : [Select Title__c,Subject_Code__c,Student__r.Roll_No__c, Score__c from External_Exam__c where Sem__c=:SemesterSelection And Subject_Code__c in : Subjectcode and Student__r.Roll_No__c =:RollNo] )
				externalExalMap.put(Subject_Code__c + Student__r.Roll_No__c , ExtExm.Score__c);
		return externalExalMap;
    }

Above code is not tested may have some syntax error or other small error which you can solve easily.
Tarun_KhandelwalTarun_Khandelwal
Hi Maheshwar,

Please try following code. Let me know in case of any dificulty,


 public List<MarkSheetDomain> getMarksheet(){        
        List<AggregateResult> subjectCodes = new List<AggregateResult>();
        List<MarkSheet> marks = new List<MarkSheet>();
        SemesterSelection = string.escapesinglequotes(SemesterSelection);
        subjectCodes = [Select Subject_Code__c code from catalog__c where Paper__c ='Maths'  And Subject_Code__c != null GROUP BY Subject_Code__c];
        
        List<String> subjectCodeList = new List<String>();
        for( AggregateResult subjectCodeRec : subjectCodes) {
            subjectCodeList.add(String.valueOf(subjectCodeRec.get('code')));
        }
        
        List<External_Exam__c> ExtExmList = [Select Title__c, Subject_Code__c, Score__c from External_Exam__c where Sem__c=:SemesterSelection And Subject_Code__c IN :subjectCodeList and Student__r.Roll_No__c =:RollNo];
        
        Map<String, External_Exam__c> subCodeToExternalExamRecMap = new Map<String, External_Exam__c>();
        for(External_Exam__c ExtExmRec : ExtExmList) {
            if(!subCodeToExternalExamRecMap.containsKey(ExtExmRec.Subject_Code__c)) {
                subCodeToExternalExamRecMap.put(ExtExmRec.Subject_Code__c, ExtExmRec);
            }
        }
        
        for (AggregateResult subjectcode : subjectCodes){
            MarkSheet mark = new MarkSheet();
            mark.SubjectCode = String.valueOf(subjectcode.get('code'));        
            mark.ExternalExam = String.valueOf(subCodeToExternalExamRecMap.get(mark.SubjectCode).Score__c);           
            marks.add(mark);
        }
        return marks;
    }