You need to sign in to do that
Don't have an account?

SOQL limit 101
I have a SOQL limit problem which I'm not sure how to fix, it concerns this class but I've tried to make it with lists and maps so the SOQL limit wouldn't exceed. And it's only that underlined part which doesn't work .
public class UpdateMainAccountTechnologyList
{
public Boolean UpdateList(Account acc)
{
Boolean hasPolycom = false;
Boolean hasDS = false;
Boolean hasCisco = false;
Boolean hasVidyo = false;
List<String> codecdetails = new List<String>();
List<String> polycoms = new List<String>();
polycoms.add('Polycom');
polycoms.add('HDX');
polycoms.add('VSX');
polycoms.add('QDX');
polycoms.add('Group');
List<String> vidyos = new List<String>();
vidyos.add('Vidyo');
vidyos.add('HD50');
vidyos.add('HD100');
vidyos.add('HD200');
List<String> ciscos = new List<String>();
ciscos.add('Cisco');
ciscos.add('Cx');
ciscos.add('Cxx');
ciscos.add('EXxx');
ciscos.add('MXxx');
ciscos.add('SXxx');
ciscos.add('MXP');
ciscos.add('Profile');
List<String> signages = new List<String>();
signages.add('Signage');
List<Asset__c> assets = [select Codec_Model__c, Codec_Details__c from Asset__c where Setup__c in (select Id from Setup__c Where Main_Account__c = :acc.Name and RecordType.Name != 'Infra') and (Codec_Model__c != null or Codec_Details__c != null)];
for(Asset__c assetti : assets)
{
codecdetails.add(assetti.Codec_Model__c + ' : ' + assetti.Codec_Details__c);
}
List<Setup__c> setupit = [select Platform_Manufacturer__c, Name, RelatedMCU__c from Setup__c where Main_Account__c = :acc.Name];
List<Setup__c> infrasetupit = [select Id, Platform_Manufacturer__c from Setup__c where Platform_Manufacturer__c != null];
// Mika R 2013-Dec-13: Map the infra setups
Map<Id, String> infraSetupMap = New Map<Id, String>();
for (Setup__c s: infrasetupit) if (!infraSetupMap.containsKey(s.Id)) infraSetupMap.put(s.Id, s.Platform_Manufacturer__c);
for(Setup__c setuppi : setupit)
{
if(setuppi.Platform_Manufacturer__c != null)
{
codecdetails.add(setuppi.Platform_Manufacturer__c);
}
//if the account has only virtual meeting rooms, we must find setup that has vmr as related mcu and find in what platform that vmr is
if(setuppi.RelatedMCU__c != null)
{
// Mika R 2013-Dec-13: Fetch infra from map
if (infraSetupMap.containsKey(setuppi.RelatedMCU__c)) codecdetails.add(infraSetupMap.get(setuppi.RelatedMCU__c));
//Setup__c MCUSetup = [select Platform_Manufacturer__c from Setup__c where Id = :setuppi.RelatedMCU__c];
/*
if(MCUSetup.Platform_Manufacturer__c != null)
{
codecdetails.add(MCUSetup.Platform_Manufacturer__c);
}
*/
}
}
for(String detail : codecdetails)
{
for(String polycom : polycoms)
{
if(detail.contains(polycom))
{
hasPolycom = true;
break;
}
}
for(String vidyo : vidyos)
{
if(detail.contains(vidyo))
{
hasVidyo = true;
break;
}
}
for(String cisco : ciscos)
{
if(detail.contains(cisco))
{
hasCisco = true;
break;
}
}
for(String signage : signages)
{
if(detail.contains(signage))
{
hasDS = true;
break;
}
}
}
String accountData = acc.Service_Type_List__c;
if(hasPolycom)
{
if(String.isBlank(accountData))
{
accountData = 'Polycom';
}
else if(!accountData.contains('Polycom'))
{
accountData += ';Polycom';
}
}
if(hasVidyo)
{
if(String.isBlank(accountData))
{
accountData = 'Vidyo';
}
else if(!accountData.contains('Vidyo'))
{
accountData += ';Vidyo';
}
}
if(hasCisco)
{
if(String.isBlank(accountData))
{
accountData = 'Cisco';
}
else if(!accountData.contains('Cisco'))
{
accountData += ';Cisco';
}
}
if(hasDS)
{
if(String.isBlank(accountData))
{
accountData = 'DS';
}
else if(!accountData.contains('DS'))
{
accountData += ';DS';
}
}
acc.Service_Type_List__c = accountData;
update acc;
return true;
}
}
public class UpdateMainAccountTechnologyList
{
public Boolean UpdateList(Account acc)
{
Boolean hasPolycom = false;
Boolean hasDS = false;
Boolean hasCisco = false;
Boolean hasVidyo = false;
List<String> codecdetails = new List<String>();
List<String> polycoms = new List<String>();
polycoms.add('Polycom');
polycoms.add('HDX');
polycoms.add('VSX');
polycoms.add('QDX');
polycoms.add('Group');
List<String> vidyos = new List<String>();
vidyos.add('Vidyo');
vidyos.add('HD50');
vidyos.add('HD100');
vidyos.add('HD200');
List<String> ciscos = new List<String>();
ciscos.add('Cisco');
ciscos.add('Cx');
ciscos.add('Cxx');
ciscos.add('EXxx');
ciscos.add('MXxx');
ciscos.add('SXxx');
ciscos.add('MXP');
ciscos.add('Profile');
List<String> signages = new List<String>();
signages.add('Signage');
List<Asset__c> assets = [select Codec_Model__c, Codec_Details__c from Asset__c where Setup__c in (select Id from Setup__c Where Main_Account__c = :acc.Name and RecordType.Name != 'Infra') and (Codec_Model__c != null or Codec_Details__c != null)];
for(Asset__c assetti : assets)
{
codecdetails.add(assetti.Codec_Model__c + ' : ' + assetti.Codec_Details__c);
}
List<Setup__c> setupit = [select Platform_Manufacturer__c, Name, RelatedMCU__c from Setup__c where Main_Account__c = :acc.Name];
List<Setup__c> infrasetupit = [select Id, Platform_Manufacturer__c from Setup__c where Platform_Manufacturer__c != null];
// Mika R 2013-Dec-13: Map the infra setups
Map<Id, String> infraSetupMap = New Map<Id, String>();
for (Setup__c s: infrasetupit) if (!infraSetupMap.containsKey(s.Id)) infraSetupMap.put(s.Id, s.Platform_Manufacturer__c);
for(Setup__c setuppi : setupit)
{
if(setuppi.Platform_Manufacturer__c != null)
{
codecdetails.add(setuppi.Platform_Manufacturer__c);
}
//if the account has only virtual meeting rooms, we must find setup that has vmr as related mcu and find in what platform that vmr is
if(setuppi.RelatedMCU__c != null)
{
// Mika R 2013-Dec-13: Fetch infra from map
if (infraSetupMap.containsKey(setuppi.RelatedMCU__c)) codecdetails.add(infraSetupMap.get(setuppi.RelatedMCU__c));
//Setup__c MCUSetup = [select Platform_Manufacturer__c from Setup__c where Id = :setuppi.RelatedMCU__c];
/*
if(MCUSetup.Platform_Manufacturer__c != null)
{
codecdetails.add(MCUSetup.Platform_Manufacturer__c);
}
*/
}
}
for(String detail : codecdetails)
{
for(String polycom : polycoms)
{
if(detail.contains(polycom))
{
hasPolycom = true;
break;
}
}
for(String vidyo : vidyos)
{
if(detail.contains(vidyo))
{
hasVidyo = true;
break;
}
}
for(String cisco : ciscos)
{
if(detail.contains(cisco))
{
hasCisco = true;
break;
}
}
for(String signage : signages)
{
if(detail.contains(signage))
{
hasDS = true;
break;
}
}
}
String accountData = acc.Service_Type_List__c;
if(hasPolycom)
{
if(String.isBlank(accountData))
{
accountData = 'Polycom';
}
else if(!accountData.contains('Polycom'))
{
accountData += ';Polycom';
}
}
if(hasVidyo)
{
if(String.isBlank(accountData))
{
accountData = 'Vidyo';
}
else if(!accountData.contains('Vidyo'))
{
accountData += ';Vidyo';
}
}
if(hasCisco)
{
if(String.isBlank(accountData))
{
accountData = 'Cisco';
}
else if(!accountData.contains('Cisco'))
{
accountData += ';Cisco';
}
}
if(hasDS)
{
if(String.isBlank(accountData))
{
accountData = 'DS';
}
else if(!accountData.contains('DS'))
{
accountData += ';DS';
}
}
acc.Service_Type_List__c = accountData;
update acc;
return true;
}
}
There are certain best practices which you would have to follow to avoid this error (to avoid hitting governor limit).
1. http://wiki.developerforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops
2. http://salesforcedeveloperblog.blogspot.com/2011/05/best-practices-of-triggers.html
3. http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_bestpract.htm
4. http://wiki.developerforce.com/page/Apex_Code_Best_Practices
Is the problem that select- sentence inside for loop?
trigger SetupChangeTechnologyListCheck on Setup__c (after insert, after update)
{
for(Setup__c s : Trigger.New)
{
if(s.Main_Account__c != null){
Account acc = [SELECT Name, Service_Type_List__c FROM Account WHERE RecordType.Name = 'Main Account' AND Name = :s.Main_Account__c];
UpdateMainAccountTechnologyList techList = new UpdateMainAccountTechnologyList();
techList.UpdateList(acc);
}
}
}
There are 2 things you can fix:
1) In UpdateMainAccountTechnologyList make sure that you do update on list of accounts outside the for loop instead of firing update for each account individiually inside for loop.
2) Other thing is as ramesh mentioned fix the trigger to do query outside the for loop. You can refer below code:
Set<String> accountSet = new Set<String>();
for(Setup__c s:trigger.new){
if(s.Main_Account__c != null)
accountSet.add(s.Main_Account__c);
}
List<Account> accList = [SELECT Name, Service_Type_List__c FROM Account WHERE RecordType.Name = 'Main Account' AND Name in :accountSet];
for(Account acc: accList){
UpdateMainAccountTechnologyList techList = new UpdateMainAccountTechnologyList();
techList.UpdateList(acc);
}
Thanks,
Y.Srinivas