+ Start a Discussion
Dharmendra Kumar 60Dharmendra Kumar 60 

How to reduce SOQL statement in controller

Object RAQ_Header__c is parent of RAQ_Question__c object. RAQ_Question related list contains list of questions that has been retrieved by a custom metadata using Trigger after insertion of RAQ_Header__c. Here I am trying to fetch the different set of question to display into different section. Can you please suggest a way to optimise the code to reduce SOQL Used -

Public with sharing class myRAQExtension {
  
    private final RAQ_Header__c raq;
    list<RAQ_Question__c> Quests;
    list<RAQ_Question__c> Quests2;
    list<RAQ_Question__c> Quests3;
    list<RAQ_Question__c> Quests4;
    list<RAQ_Question__c> Quests5;list<RAQ_Question__c> Quests6;
    list<RAQ_Question__c> Quests7;list<RAQ_Question__c> Quests8;
    list<RAQ_Question__c> Quests9;list<RAQ_Question__c> Quests10;list<RAQ_Question__c> Quests11;
    list<RAQ_Question__c> Ques= new list<RAQ_Question__c>();
    public myRAQExtension(ApexPages.StandardController stdController) {
        this.raq = (RAQ_Header__c)stdController.getRecord(); 
        Quests=[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c < 4 and Header__c=:raq.id];
        Quests2 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 4 and SR__c <= 5 and Header__c=:raq.id];
        Quests3 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 6 and SR__c <= 11 and Header__c=:raq.id];
        Quests4 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 12 and SR__c <= 14 and Header__c=:raq.id];
        Quests5 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 15 and SR__c <= 16 and Header__c=:raq.id];
        Quests6 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 17 and SR__c <= 18 and Header__c=:raq.id];
        Quests7 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 19 and SR__c <= 20 and Header__c=:raq.id];
        Quests8 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 21 and SR__c <= 22 and Header__c=:raq.id];
        Quests9 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 23 and SR__c <= 24 and Header__c=:raq.id];
        Quests10 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 25 and SR__c <= 30 and Header__c=:raq.id];
        Quests11 =[select Question__c,SR__c,Select_Answer__c,Comment__c from RAQ_Question__c where SR__c >= 31 and SR__c <= 36 and Header__c=:raq.id];
        Ques.addAll(Quests);
        Ques.addAll(Quests2);
        Ques.addAll(Quests3);
        Ques.addAll(Quests4);Ques.addAll(Quests5);Ques.addAll(Quests6);Ques.addAll(Quests7);Ques.addAll(Quests8);Ques.addAll(Quests9);Ques.addAll(Quests10);
        Ques.addAll(Quests11);
       
    }
    public list<RAQ_Question__c> getQuests() {       
        return Quests;
    }
    public list<RAQ_Question__c> getQuests2() {       
        return Quests2;
    }
     public list<RAQ_Question__c> getQuests3() {       
        return Quests3;
    }
    public list<RAQ_Question__c> getQuests4() {       
        return Quests4;
    }
       public list<RAQ_Question__c> getQuests5() {       
        return Quests5;
       }
    public list<RAQ_Question__c> getQuests6() {       
        return Quests6;
       }
    public list<RAQ_Question__c> getQuests7() {       
        return Quests7;
       }
    public list<RAQ_Question__c> getQuests8() {       
        return Quests8;
       }
    public list<RAQ_Question__c> getQuests9() {       
        return Quests9;
       }
    public list<RAQ_Question__c> getQuests10() {       
        return Quests10;
       }
        public list<RAQ_Question__c> getQuests11() {       
        return Quests11;
       }
  
   
public pagereference save(){  
 try{
 update Ques;
 update raq;
     ApexPages.addMessage(new ApexPages.message(
        ApexPages.SEVERITY.Confirm,
        'Record successfully saved'
    ));             
}
catch (Exception ex) { 
        ex.getMessage();
}     
return null;
}
}
Best Answer chosen by Dharmendra Kumar 60
Rounak SharmaRounak Sharma
hello Dharmendra,

Instead of having query and List you should go with Map.
for ex: you are getting the id from Trigger. then in that case you can use
Map<id,sObject> idMap = new Map<id,sObject>();
for(sObject sObj : idMap.Values()){
}
Please let me know if it helped you in any way.
Thanks

All Answers

Rounak SharmaRounak Sharma
hello Dharmendra,

Instead of having query and List you should go with Map.
for ex: you are getting the id from Trigger. then in that case you can use
Map<id,sObject> idMap = new Map<id,sObject>();
for(sObject sObj : idMap.Values()){
}
Please let me know if it helped you in any way.
Thanks
This was selected as the best answer
Rounak SharmaRounak Sharma
As I can see you are querying from the same object i.e., RAQ_Question__c . and you are just putting multiple conditions in the where caluse. you can query once and put the if condition and return Quest.
 
Dharmendra Kumar 60Dharmendra Kumar 60
Thanks Rounak . I got this .