Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rajat MahajanRajat Mahajan 

Your help needed - please a little urgent

Hi All,

 

Could you help me with the following piece of code :

 

----------------------------------------------------------------------------------------------------------

 

if(acclst.size()>0) {

for(Account acc:acclst) {

//Check if Account have contact/Opportunity associated with it
opplst = [select Id,AccountId from Opportunity where AccountId =:acc.Id];
conlst = [select Id from Contact where AccountId in:acc.Id];

if(opplst.size()==0&&conlst.size()==0) {
/*
* If Account do not have the contacts and opportunities associated to it
* then add those account Ids in the set
*/

sAccId.add(acc.Id);
}
else {
sAccId_CO.add(acc.Id);
}

}

-----------------------------------------------------------------------------------------------------

 

The problem : I am running this through a trigger, and since a query should not be in loops i am hitting TOO MANY SQL QUERY exception. Could you please have a look at the code and modify it to make it work for me ?

 

Your help will be really appreciated,

Rajat

Best Answer chosen by Admin (Salesforce Developers) 
Naidu PothiniNaidu Pothini
if(acclst.size()>0) 
{
	List<Id> accIds = new Lsit<Id>();
	
	for( Account ac : accLst)
	{
		accIds.add(ac.Id);
	}
	
	Map<Id, Integer> accConMap = new Map<Id, Integer>();
	Map<Id, Integer> accOppMap = new Map<Id, Integer>();
	
	for(AggregateResult ar : [SELECT Count(Id) cnt, AccountId accId FROM Contact WHERE AccountId IN :accIds GROUP BY AccountId])
	{
		accConMap.put(String.valueOf(ar.get('accId')), Integer.valueOf(ar.get('cnt')));
	}
	
	for(AggregateResult ar : [SELECT Count(Id) cnt, AccountId accId FROM Opportunity WHERE AccountId IN :accIds GROUP BY AccountId])
	{
		accOppMap.put(String.valueOf(ar.get('accId')), Integer.valueOf(ar.get('cnt')));
	}
	
	
	for(Account acc:acclst) 
	{
		if(accConMap.get(acc.Id) == 0 && accOpp.get(acc.Id) == 0)
		{
			sAccId.add(acc.Id);
		}
		else 
		{
			sAccId_CO.add(acc.Id);
		}
	}
	
}

 I guess this should work. let me know if it doesnt work.

All Answers

Naidu PothiniNaidu Pothini
if(acclst.size()>0) 
{
	List<Id> accIds = new Lsit<Id>();
	
	for( Account ac : accLst)
	{
		accIds.add(ac.Id);
	}
	
	Map<Id, Integer> accConMap = new Map<Id, Integer>();
	Map<Id, Integer> accOppMap = new Map<Id, Integer>();
	
	for(AggregateResult ar : [SELECT Count(Id) cnt, AccountId accId FROM Contact WHERE AccountId IN :accIds GROUP BY AccountId])
	{
		accConMap.put(String.valueOf(ar.get('accId')), Integer.valueOf(ar.get('cnt')));
	}
	
	for(AggregateResult ar : [SELECT Count(Id) cnt, AccountId accId FROM Opportunity WHERE AccountId IN :accIds GROUP BY AccountId])
	{
		accOppMap.put(String.valueOf(ar.get('accId')), Integer.valueOf(ar.get('cnt')));
	}
	
	
	for(Account acc:acclst) 
	{
		if(accConMap.get(acc.Id) == 0 && accOpp.get(acc.Id) == 0)
		{
			sAccId.add(acc.Id);
		}
		else 
		{
			sAccId_CO.add(acc.Id);
		}
	}
	
}

 I guess this should work. let me know if it doesnt work.

This was selected as the best answer
Anu Raj.ax1269Anu Raj.ax1269

Hi

Please take out your query from for loop and use map. This will solve this error. The error is because it is hitting governor limits.

 

Thanks 

Anu
Rajat MahajanRajat Mahajan

Man Thanks!!!!! 

Just a little code modification, but it worked !!