Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Arun KArun K 

Too many soql queries problem

I am getting an error "Too many soql queries" when there is update method as there is soql in for loop

 

Please help me in writing soql outside for loop

 

the code for this is 

 

trigger UpdateCallString on Call_String__C (after update) {

    set<Id> sUserId = new set<Id>();
    map<String,Integer> mapNewCallbyother = new map<String,Integer>();    
    List<Call_String__C> lstCallString = [select id, Account__r.Co_Owner__c, Account__r.Name, Original_CallString__c,Updated_CallString_plist__c from Call_String__C where id IN: trigger.newmap.keyset()];
    for(Call_String__C cl:lstCallString){ 
    Call_String__c oldval = Trigger.oldMap.get(cl.Id);
        if(cl.Account__r.Co_Owner__c != null && cl.Account__r.Co_Owner__c != ''){
            String[] arrOwnerid = cl.Account__r.Co_Owner__c.split(';');
            for(String str: arrOwnerid){
                if(str != null && str != ''){
                    sUserId.add(str.trim());                    
                }
            }
        }
       If(cl.Updated_CallString_plist__c!=null &&cl.Updated_CallString_plist__c!='')
       {
        mapNewCallbyother.put(cl.Account__r.Name,integer.valueOf(cl.Updated_CallString_plist__c));
       }
       else{
       mapNewCallbyother.put(cl.Account__r.Name,integer.valueOf(oldval.Original_CallString__c));
       }
    }
    List<Account> lstAccount = [Select Total_Calls_Formula__c,called_by_other__c , OwnerId, Co_Owner__c, Name from Account where ownerId IN: sUserId and Name in: mapNewCallbyother.keyset()];
   
    for(Account acc: lstAccount){
        String s='%' + acc.name+'%'; 
        String str = acc.Co_Owner__c; 
        List<String> sArr = str.split(';', 0);
        
        list<account> acclist = [Select name,OwnerId,Territory_Name__c,Total_Calls_Formula__c,(select Updated_CallString_plist__c,Original_CallString__c from Call_Strings__r) from account where ownerid!= :acc.Ownerid and name like :s and OwnerId in :sArr ];
         Integer totalothercall = 0;
        for(Account ac:acclist){
            system.debug('#######'+ac.Total_Calls_Formula__c);
            
            for(Call_String__c cs: ac.Call_Strings__r){
                if(cs.Updated_CallString_plist__c != '' && cs.Updated_CallString_plist__c!=null)
                totalothercall += integer.valueOf(cs.Updated_CallString_plist__c);
               else
               totalothercall += integer.valueOf(cs.Original_CallString__c);
              
            }
            //totalothercall += integer.valueOf(ac.Total_Calls_Formula__c);
        }
        acc.called_by_other__c = 0;
        acc.called_by_other__c = totalothercall;// + mapNewCallbyother.get(acc.Name);
    }
    if(lstAccount.size() > 0){
        update lstAccount;
    }
}

 

 

 

 

souvik9086souvik9086

use this

 

trigger UpdateCallString on Call_String__C (after update) {
List<Account> accListToBeUpdated = new List<Account>();
set<Id> sUserId = new set<Id>();
map<String,Integer> mapNewCallbyother = new map<String,Integer>();
List<Call_String__C> lstCallString = [select id, Account__r.Co_Owner__c, Account__r.Name, Original_CallString__c,Updated_CallString_plist__c from Call_String__C where id IN: trigger.newmap.keyset()];
for(Call_String__C cl:lstCallString){
Call_String__c oldval = Trigger.oldMap.get(cl.Id);
if(cl.Account__r.Co_Owner__c != null && cl.Account__r.Co_Owner__c != ''){
String[] arrOwnerid = cl.Account__r.Co_Owner__c.split(';');
for(String str: arrOwnerid){
if(str != null && str != ''){
sUserId.add(str.trim());
}
}
}
If(cl.Updated_CallString_plist__c!=null &&cl.Updated_CallString_plist__c!='')
{
mapNewCallbyother.put(cl.Account__r.Name,integer.valueOf(cl.Updated_CallString_plist__c));
}
else{
mapNewCallbyother.put(cl.Account__r.Name,integer.valueOf(oldval.Original_CallString__c));
}
}
List<Account> lstAccount = [Select Total_Calls_Formula__c,called_by_other__c , OwnerId, Co_Owner__c, Name from Account where ownerId IN: sUserId and Name in: mapNewCallbyother.keyset()];

for(Account acc: lstAccount){
String s='%' + acc.name+'%';
String str = acc.Co_Owner__c;
List<String> sArr = str.split(';', 0);

list<account> acclist = [Select name,OwnerId,Territory_Name__c,Total_Calls_Formula__c,(select Updated_CallString_plist__c,Original_CallString__c from Call_Strings__r) from account where ownerid!= :acc.Ownerid and name like :s and OwnerId in :sArr ];
Integer totalothercall = 0;
for(Account ac:acclist){
system.debug('#######'+ac.Total_Calls_Formula__c);

for(Call_String__c cs: ac.Call_Strings__r){
if(cs.Updated_CallString_plist__c != '' && cs.Updated_CallString_plist__c!=null)
totalothercall += integer.valueOf(cs.Updated_CallString_plist__c);
else
totalothercall += integer.valueOf(cs.Original_CallString__c);

}
//totalothercall += integer.valueOf(ac.Total_Calls_Formula__c);
}
acc.called_by_other__c = 0;
acc.called_by_other__c = totalothercall;// + mapNewCallbyother.get(acc.Name);
accListToBeUpdated.add(acc);
}
if(accListToBeUpdated.size() > 0){
update accListToBeUpdated;
}
}

 

If this post is helpful please throw Kudos.If this post solves your problem kindly mark it as solution.

Thanks
Arun KArun K

That still not solved the issue

 

Issue is soql inside for loop

 

list<account> acclist = [Select name,OwnerId,Territory_Name__c,Total_Calls_Formula__c,(select Updated_CallString_plist__c,Original_CallString__c from Call_Strings__r) from account where ownerid!= :acc.Ownerid and name like :s and OwnerId in :sArr ];
Integer totalothercall = 0;

 

 

 
Arun KArun K

So I have vf page where I am updating field in callstring 

 

and code for this is...

 

public void saveme() 
    {  
        System.debug('In Save Me Method===');
    try
     {
        Account updatedAcc;
      
        List<Account> updatedAccList = new List<Account>();
        List<Call_String__c> updatedStringList = new List<Call_String__c>();
       for(Wrapper wrapObj:ListCallplanWrapper)
        {
            updatedAcc = new Account();
            updatedAcc = wrapObj.acc;
            
            if (wrapObj.acc.Call_Strings__r!= null && !wrapObj.acc.Call_Strings__r.isEmpty())
            {
                updatedStringList.addAll(wrapObj.acc.Call_Strings__r);
            }           
             updatedAccList.add(updatedAcc);             
        }
        
        update updatedStringList;
       update updatedAccList;
    }
    
    catch(Exception e)
    {
        System.debug('Exception in Saving records == '+e.getMessage());
    }
     callPlanIndicators();
          getCallplanAccounts();   
    
    }

 So when there is an update action trigger fires and it is showing too many soql queries

the error message is 

Too many SOQL queries: 101
Error is in expression '{!saveme}' in page axtria_callplan_primarycare

 
An unexpected error has occurred. Your development organization has been notified.

 

But when I see notified mail

 

Apex script unhandled trigger exception by user/organization: 005i00f0000dRnR/00Di0000dfg000asIQWN

Visualforce Page: /apex/primarycare



caused by: System.LimitException: Too many SOQL queries: 101

Trigger.UpdateCallString1: line 31, column 1

 

 

line no 31 is 

 

list<account> acclist = [Select name,OwnerId,Territory_Name__c,Total_Calls_Formula__c,(select Updated_CallString_plist__c,Original_CallString__c from Call_Strings__r) from account where ownerid!= :acc.Ownerid and name like :s and OwnerId in :sArr ];
Integer totalothercall = 0;

 

Help me to overcome this error

 
Val ValinoVal Valino

Is line 31 inside a for loop? Line 31 may not be the problem. Line 31 may just be the query where the limit is met.
Arun KArun K

Lyes line 31 is inside for loop an  it is colored in blue

 

for refernce

 

 

trigger UpdateCallString on Call_String__C (after update) {
List<Account> accListToBeUpdated = new List<Account>();
set<Id> sUserId = new set<Id>();
map<String,Integer> mapNewCallbyother = new map<String,Integer>();
List<Call_String__C> lstCallString = [select id, Account__r.Co_Owner__c, Account__r.Name, Original_CallString__c,Updated_CallString_plist__c from Call_String__C where id IN: trigger.newmap.keyset()];
for(Call_String__C cl:lstCallString){
Call_String__c oldval = Trigger.oldMap.get(cl.Id);
if(cl.Account__r.Co_Owner__c != null && cl.Account__r.Co_Owner__c != ''){
String[] arrOwnerid = cl.Account__r.Co_Owner__c.split(';');
for(String str: arrOwnerid){
if(str != null && str != ''){
sUserId.add(str.trim());
}
}
}
If(cl.Updated_CallString_plist__c!=null &&cl.Updated_CallString_plist__c!='')
{
mapNewCallbyother.put(cl.Account__r.Name,integer.valueOf(cl.Updated_CallString_plist__c));
}
else{
mapNewCallbyother.put(cl.Account__r.Name,integer.valueOf(oldval.Original_CallString__c));
}
}
List<Account> lstAccount = [Select Total_Calls_Formula__c,called_by_other__c , OwnerId, Co_Owner__c, Name from Account where ownerId IN: sUserId and Name in: mapNewCallbyother.keyset()];

for(Account acc: lstAccount){
String s='%' + acc.name+'%';
String str = acc.Co_Owner__c;
List<String> sArr = str.split(';', 0);

list<account> acclist = [Select name,OwnerId,Territory_Name__c,Total_Calls_Formula__c,(select Updated_CallString_plist__c,Original_CallString__c from Call_Strings__r) from account where ownerid!= :acc.Ownerid and name like :s and OwnerId in :sArr ];
Integer totalothercall = 0;
for(Account ac:acclist){
system.debug('#######'+ac.Total_Calls_Formula__c);

for(Call_String__c cs: ac.Call_Strings__r){
if(cs.Updated_CallString_plist__c != '' && cs.Updated_CallString_plist__c!=null)
totalothercall += integer.valueOf(cs.Updated_CallString_plist__c);
else
totalothercall += integer.valueOf(cs.Original_CallString__c);

}
//totalothercall += integer.valueOf(ac.Total_Calls_Formula__c);
}
acc.called_by_other__c = 0;
acc.called_by_other__c = totalothercall;// + mapNewCallbyother.get(acc.Name);
accListToBeUpdated.add(acc);
}
if(accListToBeUpdated.size() > 0){
update accListToBeUpdated;
}
}
Val ValinoVal Valino

You have to take that query out of that loop. Try putting  the values you need for that query in a map outside of that for loop and then in the for loop, call the value you need from the map by using the key.