+ Start a Discussion
Avesh LakhaAvesh Lakha 

Content cannot be displayed: Too many SOQL queries: 101 in VF page

I am facing this error when I am refreshing my VF page. I know this is because SOQL query is written inside the for loops but I am struggling with removing the SOQL outside the for loop. 

Here is the method which is called when constructor runs when the page loads

Can anyone please help me in this case.

Thanks
Avesh Lakha​
public List<selectedProductClass> getSelectedProductClassString(){
       
       
        List<List<String>> lstProductsInCategory = new List<List<String>>();
        lstAllAccountProductClassString = new List<selectedProductClass>();
        
        Map <List<String>,String> mapProductCategory = new Map <List<String>,String>();
        Map <List<String>,String> mapShow = new Map <List<String>,String>();
        
        
       for(Account_Product__c accProducts : [SELECT Id,Account__c,Product_Category__c,Show__c,Products__c from Account_Product__c where Account__c =: this.iAccountId]){
                   
           for(String pd : accProducts.Products__c.split(';')) {
           selectedProductClass objClass = new selectedProductClass ();
                objClass.selectedShow = accProducts.Show__c ;
                objClass.selectedCategory = accProducts.Product_Category__c;
                objClass.selectProduct = pd;
                                                 
           Product__c[] prod  = [SELECT Id from Product__c where  Name =:pd AND Product_Category__c =: accProducts.Product_Category__c AND Show__c =:accProducts.Show__c limit 1];
              if (prod.size() > 0)
                objClass.ProductId = prod[0].Id;
                lstAllAccountProductClassString.add(objClass); 
            }
               lstProductsInCategory.add(accProducts.Products__c.split(';'));
               mapProductCategory.put(accProducts.Products__c.split(';'),accProducts.Product_Category__c);
               mapShow.put(accProducts.Products__c.split(';'),accProducts.Show__c);
        }
        

        return lstAllAccountProductClassString;
    }

 
Best Answer chosen by Avesh Lakha
Leo10Leo10
Hi,

I don't know your exact scenario but there is no a relationship between the objects Account_Product__c and Product__c. In this case, you can use the below code snippet.     
 
public List<selectedProductClass> getSelectedProductClassString(){
       
       
        List<List<String>> lstProductsInCategory = new List<List<String>>();
        lstAllAccountProductClassString = new List<selectedProductClass>();
        
        Map <List<String>,String> mapProductCategory = new Map <List<String>,String>();
        Map <List<String>,String> mapShow = new Map <List<String>,String>();
        
        Product__c[] prod = getProducts();

       for(Account_Product__c accProducts : [SELECT Id,Account__c,Product_Category__c,Show__c,Products__c from Account_Product__c where Account__c =: this.iAccountId]){
                   
           for(String pd : accProducts.Products__c.split(';')) {
              selectedProductClass objClass = new selectedProductClass ();
              objClass.selectedShow = accProducts.Show__c ;
              objClass.selectedCategory = accProducts.Product_Category__c;
              objClass.selectProduct = pd;
                                                 
            for(Product__c p:prod){
                if(p.Name ==pd && p.Product_Category__c == accProducts.Product_Category__c && p.Show__c == accProducts.Show__c)
                {
                  objClass.ProductId = p.Id;
                  break;
                }
            }
                lstAllAccountProductClassString.add(objClass); 
            }
            
            
               lstProductsInCategory.add(accProducts.Products__c.split(';'));
               mapProductCategory.put(accProducts.Products__c.split(';'),accProducts.Product_Category__c);
               mapShow.put(accProducts.Products__c.split(';'),accProducts.Show__c);
        }
        

        return lstAllAccountProductClassString;
    }
    @readonly
    public static List<Product__c> getProducts (){

      return[SELECT Id,Name,Product_Category__c,Show__c from Product__c];

    }

Warm Regards,
Nabeel KT

All Answers

Leo10Leo10
Hi,

I don't know your exact scenario but there is no a relationship between the objects Account_Product__c and Product__c. In this case, you can use the below code snippet.     
 
public List<selectedProductClass> getSelectedProductClassString(){
       
       
        List<List<String>> lstProductsInCategory = new List<List<String>>();
        lstAllAccountProductClassString = new List<selectedProductClass>();
        
        Map <List<String>,String> mapProductCategory = new Map <List<String>,String>();
        Map <List<String>,String> mapShow = new Map <List<String>,String>();
        
        Product__c[] prod = getProducts();

       for(Account_Product__c accProducts : [SELECT Id,Account__c,Product_Category__c,Show__c,Products__c from Account_Product__c where Account__c =: this.iAccountId]){
                   
           for(String pd : accProducts.Products__c.split(';')) {
              selectedProductClass objClass = new selectedProductClass ();
              objClass.selectedShow = accProducts.Show__c ;
              objClass.selectedCategory = accProducts.Product_Category__c;
              objClass.selectProduct = pd;
                                                 
            for(Product__c p:prod){
                if(p.Name ==pd && p.Product_Category__c == accProducts.Product_Category__c && p.Show__c == accProducts.Show__c)
                {
                  objClass.ProductId = p.Id;
                  break;
                }
            }
                lstAllAccountProductClassString.add(objClass); 
            }
            
            
               lstProductsInCategory.add(accProducts.Products__c.split(';'));
               mapProductCategory.put(accProducts.Products__c.split(';'),accProducts.Product_Category__c);
               mapShow.put(accProducts.Products__c.split(';'),accProducts.Show__c);
        }
        

        return lstAllAccountProductClassString;
    }
    @readonly
    public static List<Product__c> getProducts (){

      return[SELECT Id,Name,Product_Category__c,Show__c from Product__c];

    }

Warm Regards,
Nabeel KT
This was selected as the best answer
Avesh LakhaAvesh Lakha

Hi Nabeel 

Thanks for your help! till now working perfectly doing more testing by using different cases will get back to you in case of face any issue.

Thanks a lot for your help!

Regards,
Avesh Lakha