function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
kittu9kittu9 

Too many SOQL Error

public class PopulateCorporateFields {

    public List<Corporate_Account_List_Assignment__c> calist { set; get; }
 
    public PopulateCorporateFields(ApexPages.StandardController controller) {

    }
    
    public void updateFields()
    {
     calist = [SELECT Account_Name__c,Corporate_Partnership_Director_1__c,Corporate_Partnership_Director_2__c,Corporate_Partnership_Director_3__c,Corporate_Partnership_Director_4__c,Corporate_Partnership_Director_5__c,Marketing_Solutions_1__c, Marketing_Solutions_2__c, Marketing_Solutions_3__c, Media_Services_1__c, Media_Services_2__c, Media_Services_3__c,Digital_Manager_1__c,Digital_Manager_2__c,Digital_Manager_3__c,Digital_Manager_4__c,Digital_Manager_5__c, Id,Name,Ultimate_Parent_Name__c FROM Corporate_Account_List_Assignment__c];     
         
     for (Corporate_Account_List_Assignment__c ca : calist)
     {
     ca.Corporate_Partnership_Director_1__c = null;
     ca.Corporate_Partnership_Director_2__c = null;
     ca.Corporate_Partnership_Director_3__c = null;
     ca.Corporate_Partnership_Director_4__c = null; 
     ca.Corporate_Partnership_Director_5__c = null;
     
     ca.Digital_Manager_1__c = null;
     ca.Digital_Manager_2__c = null;
     ca.Digital_Manager_3__c = null; 
     ca.Digital_Manager_4__c = null;
     ca.Digital_Manager_5__c = null;
     
     ca.Marketing_Solutions_1__c = null;
     ca.Marketing_Solutions_2__c = null;
     ca.Marketing_Solutions_3__c = null;
     
     ca.Media_Services_1__c = null;
     ca.Media_Services_2__c = null; 
     ca.Media_Services_3__c = null;
      System.debug('******ID********'+ca.Id);
      Account ac = [SELECT Name,ParentId,Id FROM Account where Id =: ca.Account_Name__c];
      
      List<AccountTeamMember> atmcpd =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Corporate Partnership Director'  ];
      
      List<AccountTeamMember> atmMS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'IMS' AND Isdeleted = FALSE  ];       
      
      List<AccountTeamMember> atmMDS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE  ];
      
     List<AccountTeamMember> atmDM =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE  ];
      
        if(atmcpd!= null || atmcpd.size() > 0 )
        {
        // User u = [select Id,Name from user where Id=: atm.get(0).UserId];
        //System.debug('******Size*******'+atmcpd.size()+'**********'+atmcpd.get(0).UserId);
         if(atmcpd.size() > 0)
         ca.Corporate_Partnership_Director_1__c = atmcpd.get(0).UserId;
         
          if(atmcpd.size()>1)
           ca.Corporate_Partnership_Director_2__c = atmcpd.get(1).UserId;
         
           if(atmcpd.size()>2)
           ca.Corporate_Partnership_Director_3__c = atmcpd.get(2).UserId;         
           
           if(atmcpd.size()>3)
           ca.Corporate_Partnership_Director_4__c = atmcpd.get(3).UserId;
           
          if(atmcpd.size()>4)
           ca.Corporate_Partnership_Director_5__c = atmcpd.get(4).UserId;
        }
        
        if(atmms != null || atmms.size() > 0)
        {
          if(atmms.size() > 0)
          ca.Marketing_Solutions_1__c  = atmms.get(0).UserId;
          
          if(atmms.size() > 1)
          ca.Marketing_Solutions_2__c = atmms.get(1).UserId;
          
          if(atmms.size() > 2)
          ca.Marketing_Solutions_3__c = atmms.get(2).UserId;

        }
      
      
        if(atmmds != null || atmmds.size() > 0)
        {
          if(atmmds.size() > 0)
          ca.Media_Services_1__c = atmmds.get(0).UserId;
          
          if(atmmds.size() > 1)
          ca.Media_Services_2__c = atmmds.get(1).UserId;
          
          if(atmmds.size() > 2) 
          ca.Media_Services_3__c = atmmds.get(2).UserId;
          
        }
        if(atmdm != null || atmdm.size() > 0)
        {
         if(atmdm.size() > 0)
         ca.Digital_Manager_1__c = atmdm.get(0).UserId;
         
         if(atmdm.size() > 1)
          ca.Digital_Manager_2__c = atmdm.get(1).UserId;
          
         if(atmdm.size() > 2)
          ca.Digital_Manager_3__c  = atmdm.get(2).UserId;
          
         if(atmdm.size() > 3)
          ca.Digital_Manager_4__c  = atmdm.get(3).UserId;
          
          if(atmdm.size() > 4)
          ca.Digital_Manager_5__c  = atmdm.get(4).UserId;
        }
      }               
      update calist;
    }

}

Getting too many soql queries error getting. Pleas help me on this how to optimize it.

Avidev9Avidev9

Well you have lots of SOQL statements inside for loop, whihc is not a good practice. You may consider using collections to avoid soql inside for loops. I am highlighting the problem areas in your code.

 

public class PopulateCorporateFields {

    public List < Corporate_Account_List_Assignment__c > calist {
        set;
        get;
    }

    public PopulateCorporateFields(ApexPages.StandardController controller) {

    }

    public void updateFields() {
        calist = [SELECT Account_Name__c, Corporate_Partnership_Director_1__c, Corporate_Partnership_Director_2__c, Corporate_Partnership_Director_3__c, Corporate_Partnership_Director_4__c, Corporate_Partnership_Director_5__c, Marketing_Solutions_1__c, Marketing_Solutions_2__c, Marketing_Solutions_3__c, Media_Services_1__c, Media_Services_2__c, Media_Services_3__c, Digital_Manager_1__c, Digital_Manager_2__c, Digital_Manager_3__c, Digital_Manager_4__c, Digital_Manager_5__c, Id, Name, Ultimate_Parent_Name__c FROM Corporate_Account_List_Assignment__c];

// IMPORTANT : The above query doesnt have any filters/where clause and hence will bring in all the data//
for (Corporate_Account_List_Assignment__c ca: calist) { ca.Corporate_Partnership_Director_1__c = null; ca.Corporate_Partnership_Director_2__c = null; ca.Corporate_Partnership_Director_3__c = null; ca.Corporate_Partnership_Director_4__c = null; ca.Corporate_Partnership_Director_5__c = null; ca.Digital_Manager_1__c = null; ca.Digital_Manager_2__c = null; ca.Digital_Manager_3__c = null; ca.Digital_Manager_4__c = null; ca.Digital_Manager_5__c = null; ca.Marketing_Solutions_1__c = null; ca.Marketing_Solutions_2__c = null; ca.Marketing_Solutions_3__c = null; ca.Media_Services_1__c = null; ca.Media_Services_2__c = null; ca.Media_Services_3__c = null; System.debug('******ID********' + ca.Id);

//ALL these query are inside for loop, and hence adding upto the limit Account ac = [SELECT Name, ParentId, Id FROM Account where Id = : ca.Account_Name__c]; List < AccountTeamMember > atmcpd = [SELECT Id, TeamMemberRole, UserId, IsDeleted FROM AccountTeamMember WHERE AccountId = : ac.Id AND TeamMemberRole = 'Corporate Partnership Director']; List < AccountTeamMember > atmMS = [SELECT Id, TeamMemberRole, UserId, IsDeleted FROM AccountTeamMember WHERE AccountId = : ac.Id AND TeamMemberRole = 'IMS' AND Isdeleted = FALSE ]; List < AccountTeamMember > atmMDS = [SELECT Id, TeamMemberRole, UserId, IsDeleted FROM AccountTeamMember WHERE AccountId = : ac.Id AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE ]; List < AccountTeamMember > atmDM = [SELECT Id, TeamMemberRole, UserId, IsDeleted FROM AccountTeamMember WHERE AccountId = : ac.Id AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE ]; if (atmcpd != null || atmcpd.size() > 0) { // User u = [select Id,Name from user where Id=: atm.get(0).UserId]; //System.debug('******Size*******'+atmcpd.size()+'**********'+atmcpd.get(0).UserId); if (atmcpd.size() > 0) ca.Corporate_Partnership_Director_1__c = atmcpd.get(0).UserId; if (atmcpd.size() > 1) ca.Corporate_Partnership_Director_2__c = atmcpd.get(1).UserId; if (atmcpd.size() > 2) ca.Corporate_Partnership_Director_3__c = atmcpd.get(2).UserId; if (atmcpd.size() > 3) ca.Corporate_Partnership_Director_4__c = atmcpd.get(3).UserId; if (atmcpd.size() > 4) ca.Corporate_Partnership_Director_5__c = atmcpd.get(4).UserId; } if (atmms != null || atmms.size() > 0) { if (atmms.size() > 0) ca.Marketing_Solutions_1__c = atmms.get(0).UserId; if (atmms.size() > 1) ca.Marketing_Solutions_2__c = atmms.get(1).UserId; if (atmms.size() > 2) ca.Marketing_Solutions_3__c = atmms.get(2).UserId; } if (atmmds != null || atmmds.size() > 0) { if (atmmds.size() > 0) ca.Media_Services_1__c = atmmds.get(0).UserId; if (atmmds.size() > 1) ca.Media_Services_2__c = atmmds.get(1).UserId; if (atmmds.size() > 2) ca.Media_Services_3__c = atmmds.get(2).UserId; } if (atmdm != null || atmdm.size() > 0) { if (atmdm.size() > 0) ca.Digital_Manager_1__c = atmdm.get(0).UserId; if (atmdm.size() > 1) ca.Digital_Manager_2__c = atmdm.get(1).UserId; if (atmdm.size() > 2) ca.Digital_Manager_3__c = atmdm.get(2).UserId; if (atmdm.size() > 3) ca.Digital_Manager_4__c = atmdm.get(3).UserId; if (atmdm.size() > 4) ca.Digital_Manager_5__c = atmdm.get(4).UserId; } } update calist; } }

 Have a look here on how to bulkify your code to avoid too many soql : http://wiki.developerforce.com/page/Best_Practice:_Bulkify_Your_Code

kittu9kittu9

thanks for your reply. How to use collection at that part is my concern. Could you please help me on this.

kittu9kittu9

public class PopulateCorporateFields {

public List<Corporate_Account_List_Assignment__c> calist { set; get; }

public PopulateCorporateFields(ApexPages.StandardController controller) {

}

public void updateFields()
{
List<Id> AccIds = new List<Id>();

calist = [SELECT Account_Name__c,Corporate_Partnership_Director_1__c,Corporate_Partnership_Director_2__c,Corporate_Partnership_Director_3__c,Corporate_Partnership_Director_4__c,Corporate_Partnership_Director_5__c,Marketing_Solutions_1__c, Marketing_Solutions_2__c, Marketing_Solutions_3__c, Media_Services_1__c, Media_Services_2__c, Media_Services_3__c,Digital_Manager_1__c,Digital_Manager_2__c,Digital_Manager_3__c,Digital_Manager_4__c,Digital_Manager_5__c, Id,Name,Ultimate_Parent_Name__c FROM Corporate_Account_List_Assignment__c ];
//Account ac = [SELECT Name,ParentId,Id FROM Account where Id =: ca.Account_Name__c];

for(Corporate_Account_List_Assignment__c Cal:calist )
AccIds.add(Cal.Account_Name__c);
List<AccountTeamMember> atmcpd =[ SELECT Id,TeamMemberRole,UserId, IsDeleted,AccountId FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Corporate Partnership Director' ];
List<AccountTeamMember> atmMS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted,AccountId FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Marketing Solutions' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmMDS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted,AccountId FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmDM =[ SELECT Id,TeamMemberRole,UserId,AccountId, IsDeleted FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE ];
System.debug('**************CPD*********'+atmcpd+'*********MS******'+atmMS+'******MDS*****'+atmMDS+'***********atmDM****'+atmDM);
for(Corporate_Account_List_Assignment__c Cal: calist)
{
integer i = 0;
for(AccountTeamMember atm:atmcpd)
{
if(Cal.Account_Name__c == atm.AccountId)
{
if(i==0 )
cal.Corporate_Partnership_Director_1__c = atm.UserId;
else if(i==1)
cal.Corporate_Partnership_Director_2__c = atm.UserId;

else if(i==2)
cal.Corporate_Partnership_Director_3__c = atm.UserId;

else if(i==3)
cal.Corporate_Partnership_Director_4__c = atm.UserId;
else if(i==4)
cal.Corporate_Partnership_Director_5__c = atm.UserId;

}
i++;
}
integer j = 0;
for(AccountTeamMember atm:atmMS)
{
if(Cal.Account_Name__c == atm.AccountId)
{
if(j==0)
cal.Marketing_Solutions_1__c = atm.UserId;
else if(j==1)
cal.Marketing_Solutions_2__c = atm.UserId;
else if(j==2)
cal.Marketing_Solutions_3__c = atm.UserId;
}
j++;
}
integer k = 0;
for(AccountTeamMember atm:atmMDS)
{
if(Cal.Account_Name__c == atm.AccountId)
{
if(k==0 )
cal.Media_Services_1__c = atm.UserId;
else if(k==1)
cal.Media_Services_2__c = atm.UserId;
else if(k==2)
cal.Media_Services_3__c = atm.UserId;
}
k++;
}
integer l = 0;
for(AccountTeamMember atm:atmDM)
{
system.debug('i value before*'+i);
if(Cal.Account_Name__c == atm.AccountId)
{
if(l==0 )
{
system.debug('i=0'+atm.UserId);
cal.Digital_Manager_1__c = atm.UserId;
}
else if(l==1)
{
cal.Digital_Manager_2__c = atm.UserId;
system.debug('i=1'+atm.UserId);
}

else if(l==2)
{
system.debug('i=2'+atm.UserId);
cal.Digital_Manager_3__c = atm.UserId;
}

else if(l==3)
{
cal.Digital_Manager_4__c = atm.UserId;
system.debug('i=3'+atm.UserId);
}
else if(l==4)
{
cal.Digital_Manager_5__c = atm.UserId;
system.debug('i=4'+atm.UserId);
}

}
l++;
}
}


system.debug('updated list'+calist);
update calist;
}

}

 

After changing also not working. pls help me on it