Hi, I have created a class for processing opportunity object based on the custom object "staging__c". But the issue is I'm executing SOQL inside for loop which is a bad practice. Can anyone help me to avoid it? Here is my code for the same please have a look. Note: My code is working. The only issue is soql which I am executing in the loop. I have commented on the issues in my code for better understanding.
global class BatchAssignment implements Database.Batchable<sObject>{
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator([SELECT Id, Individual_or_organization__c ,Orgganization_Name__c,First_Name__c,Last_Name__c,Postal_Code__c,Date_Recieved__c,Amount__c,Error_Message__c,Description__c
FROM staging__c]);
}
global void execute(Database.BatchableContext BC, List<staging__c> scope){
List<Opportunity> newOpptyList = new List<Opportunity>();
for(staging__c sc:scope){
system.debug('in for loop');
if(sc != null && sc.Individual_or_organization__c == 'I') {
system.debug('in if == I');
// calling custom method that executes soql inside loop and storing output here
Account objAccount = searchAccount(sc);
if(objAccount != null) {
// calling custom method that executes soql inside loop and storing output here
List<Contact> linkContactList = searchContact(objAccount, sc);
system.debug('contact'+linkContactList);
try{
if(linkContactList != null && !linkContactList.isEmpty()) {
upsert linkContactList;
system.debug('creating opp');
//Create new opportunity;
for(Contact objCon : linkContactList) {
newOpptyList.add(new Opportunity(Name = objCon.LastName,
StageName = 'Prospecting',
AccountId = objAccount.Id,
ContactId = objCon.Id,
CLOSEDATE=Date.today()
));
}
if(newOpptyList != null && !newOpptyList.isEmpty()) {
system.debug('insert opp');
insert newOpptyList;
}
}
}catch(Exception ex) {
system.debug('---Exception--' + ex);
}
}
}
}
}
// this method is getting called inside the loop of execute method which is bad
private Account searchAccount(staging__c scope) {
Account acc= new Account();
if(scope.Orgganization_Name__c != null && scope.Postal_Code__c != null) {
system.debug('acc not zero');
acc= [SELECT Id, Name FROM Account WHERE Name = :scope.Orgganization_Name__c AND BillingPostalCode = :scope.Postal_Code__c];
}
return acc;
}
// this method is getting called inside the loop of execute method which is bad
private List<Contact> searchContact(Account objAccount, staging__c scope) {
List<Contact> linkContactList = new List<Contact>();
if(scope.First_Name__c != null && scope.Last_Name__c != null && scope.Postal_Code__c != null) {
List<Contact> existingContactList = [SELECT Id, FirstName, LastName, MailingPostalCode FROM Contact
WHERE FirstName = :scope.First_Name__c AND LastName = :scope.Last_Name__c AND MailingPostalCode = :scope.Postal_Code__c];
//For existing contacts
system.debug('existing contact'+existingContactList);
if(existingContactList.size()>0 ) {
for(Contact objCon : existingContactList) {
objCon.AccountId = objAccount.Id;
linkContactList.add(objCon);
}
} else {
//create new contact
system.debug('into else');
linkContactList.add(new Contact(FirstName = scope.First_Name__c,
LastName = scope.Last_Name__c,
MailingPostalCode = scope.Postal_Code__c,
AccountId = objAccount.Id));
system.debug('linked contact2'+linkContactList);
}
}
return linkContactList;
}
global void finish(Database.BatchableContext BC){
system.debug('finished:::');
}
}
Hi, I have created a class for processing opportunity object based on the custom object "staging__c". But the issue is I'm executing SOQL inside for loop which is a bad practice. Can anyone help me to avoid it? Here is my code for the same please have a look. Note: My code is working. The only issue is soql which I am executing in the loop. I have commented on the issues in my code for better understanding.
global class BatchAssignment implements Database.Batchable<sObject>{
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator([SELECT Id, Individual_or_organization__c ,Orgganization_Name__c,First_Name__c,Last_Name__c,Postal_Code__c,Date_Recieved__c,Amount__c,Error_Message__c,Description__c
FROM staging__c]);
}
global void execute(Database.BatchableContext BC, List<staging__c> scope){
List<Opportunity> newOpptyList = new List<Opportunity>();
for(staging__c sc:scope){
system.debug('in for loop');
if(sc != null && sc.Individual_or_organization__c == 'I') {
system.debug('in if == I');
// calling custom method that executes soql inside loop and storing output here
Account objAccount = searchAccount(sc);
if(objAccount != null) {
// calling custom method that executes soql inside loop and storing output here
List<Contact> linkContactList = searchContact(objAccount, sc);
system.debug('contact'+linkContactList);
try{
if(linkContactList != null && !linkContactList.isEmpty()) {
upsert linkContactList;
system.debug('creating opp');
//Create new opportunity;
for(Contact objCon : linkContactList) {
newOpptyList.add(new Opportunity(Name = objCon.LastName,
StageName = 'Prospecting',
AccountId = objAccount.Id,
ContactId = objCon.Id,
CLOSEDATE=Date.today()
));
}
if(newOpptyList != null && !newOpptyList.isEmpty()) {
system.debug('insert opp');
insert newOpptyList;
}
}
}catch(Exception ex) {
system.debug('---Exception--' + ex);
}
}
}
}
}
// this method is getting called inside the loop of execute method which is bad
private Account searchAccount(staging__c scope) {
Account acc= new Account();
if(scope.Orgganization_Name__c != null && scope.Postal_Code__c != null) {
system.debug('acc not zero');
acc= [SELECT Id, Name FROM Account WHERE Name = :scope.Orgganization_Name__c AND BillingPostalCode = :scope.Postal_Code__c];
}
return acc;
}
// this method is getting called inside the loop of execute method which is bad
private List<Contact> searchContact(Account objAccount, staging__c scope) {
List<Contact> linkContactList = new List<Contact>();
if(scope.First_Name__c != null && scope.Last_Name__c != null && scope.Postal_Code__c != null) {
List<Contact> existingContactList = [SELECT Id, FirstName, LastName, MailingPostalCode FROM Contact
WHERE FirstName = :scope.First_Name__c AND LastName = :scope.Last_Name__c AND MailingPostalCode = :scope.Postal_Code__c];
//For existing contacts
system.debug('existing contact'+existingContactList);
if(existingContactList.size()>0 ) {
for(Contact objCon : existingContactList) {
objCon.AccountId = objAccount.Id;
linkContactList.add(objCon);
}
} else {
//create new contact
system.debug('into else');
linkContactList.add(new Contact(FirstName = scope.First_Name__c,
LastName = scope.Last_Name__c,
MailingPostalCode = scope.Postal_Code__c,
AccountId = objAccount.Id));
system.debug('linked contact2'+linkContactList);
}
}
return linkContactList;
}
global void finish(Database.BatchableContext BC){
system.debug('finished:::');
}
}
