+ Start a Discussion
SfDc@123.ax1389SfDc@123.ax1389 

Too Many Soql Queries-101

hi every one,
     I am trying to implement a google chart using group by post code and status. my requirement is to group it by postcode where i do have a lot of post codes and its firing an error "Too Many Soql Queries-101".code error is indicated in red.

my code goes like this:-
 
public class columntest {
 public String getChartData()
{
 return chartData;
}
 public columntest()
{
userid = userinfo.getuserid();
       u = [Select ContactId, Id, Name from User u  where Id =: userid];
       System.Debug('ContactId :' +u.ContactId);
       ctid = u.ContactId;            
       c = [select Account.Id, Id  from Contact where id =: ctid];       
       acct = [Select id, Corporate_Customer__c from account where id =: c.Account.Id];       
       aid = c.Account.Id;       
       corporateid = acct.Corporate_Customer__c;
for(Custom_report__c r:[SELECT id,Postcode__c FROM Custom_report__c where Postcode__c != null and Account_LandLord_Id__c=:corporateid])
 {
   string test1 = string.valueof(r.Postcode__c);
    if(citymap.get(test1)==null)
    {
      list1.add(r);
      citymap.put(test1,test1);
     }
  }     
 //obtain a list of picklist values
 Schema.DescribeFieldResult F = Custom_report__c.status__c.getDescribe();
 List<Schema.PicklistEntry> P = F.getPicklistValues();
 //where chart data should be stored.
 List<ChartDataItem> items = new List<ChartDataItem>();
 //iterate through each picklist value and get number of accounts
integer k=0;
for(Custom_report__c c: list1)
{
 st[k]= c.postcode__c;
 k++;
}
for(integer m=0; m<st.size();m++)
{
 if(st[m]!=null)
 size++;
}
 for(integer i=0; i<size;i++)
 {
  post = st[i];
 postCount = [select count() from Custom_report__c  where Account_LandLord_Id__c =: corporateid and Postcode__c =: post ];
 if (postcount>0)
 postsize +=1;
 }
 for(Schema.PicklistEntry pValue : P)
 {
    if(corporateid != null)
  {
    for(integer i=0; i<postsize;i++)
 {
  post = st[i];
    Count = [select count() from Custom_report__c  where status__c = :pValue.getValue() and Account_LandLord_Id__c =: corporateid and Postcode__c =: post];   
     if (Count > 0)
      items.add(new ChartDataItem(pValue.getValue() , Count.format()));
   else
     items.add(new ChartDataItem(pValue.getValue() , Count.format()));
  }
  }
    else
    {
    Count = [select count() from Custom_report__c where status__c = :pValue.getValue() and AccountId__c =: aid  ];
    if (Count > 0)
      items.add(new ChartDataItem(pValue.getValue() , Count.format()));
    else
     items.add(new ChartDataItem(pValue.getValue() , Count.format()));
    }  
 }
 
Thanks in Advance,
Newbie

Best Answer chosen by Admin (Salesforce Developers) 
uday annapareddyuday annapareddy

You are calling SOQL in a for loop which pop an error of "too many SOQL queries". You can only use 100 queries in VF page.

 

Modify your for loop like below to reduce the queries and get what u want. 

It works perfectly in my org.

 

for(Schema.PicklistEntry pValue : P)
{
integer[] counts = new integer[50];

if(corporateid != null)
{

for(AggregateResult results : [SELECT postcode__c, Count(status__c)cnt from Custom_report__c where status__c = :pValue.getValue() and Account_LandLord_Id__c =: corporateid group by postcode__c order by postcode__c])
{
system.debug('aggregateresults'+results);

for(integer tl=0;tl<size;tl++)
{
if(st[tl]==results.get('postcode__c'))
{
if(counts[tl]== null)
counts[tl] = 0;
counts[tl] =integer.valueof(results.get('cnt'));
break;
}
else
{
if(counts[tl]== null)
counts[tl] = 0;
counts[tl] +=0;
}

}
system.debug('-----counts-------'+counts);

postcodes.add(string.valueof(results.get('postcode__c')));

status.add(string.valueof(pValue.getValue()));

}
for(integer t2=0;t2<size;t2++)
{
if(counts[t2]!=null)
newcounts.add(string.valueof(counts[t2]));
else
newcounts.add('0');
}
system.debug('-----newcounts-------'+newcounts);

system.debug('-----postcodes after loop-------'+postcodes);
system.debug('-----status after loop-------'+status);
system.debug('-----counts-------'+newcounts);
/* for(integer tl=0;tl<size;tl++)
{
if(st[tl]==postcodes[tl])
count=1;
else
count=0;
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
} */
// items.add(new ChartDataItem(pValue.getValue() , results.format()));
/* List<Account> accts = [select Id, Name, BillingCity, BillingState, BillingPostalCode from Account];
for(Account a : accts) {
// AccountToString aString = new AccountToString( Name = a.Name, BillingAddress = a.BillingAddress, BillingCity = a.BillingCity, BillingState = a.BillingState, BillingPostalCode = a.BillingPostalCode);
leadlist.add(a); // aStringList is a public class variable
}
for(integer i=0; i<5;i++)
{
post = st[i];
system.debug('loop'+i+post);
Count = [select count() from Custom_report__c where status__c = :pValue.getValue() and Account_LandLord_Id__c =: corporateid and Postcode__c =: post];
system.debug('&&&&&&&&&&&count'+count);
if (Count > 0)
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
else
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
}
*/

}

else
{
Count = [select count() from Custom_report__c where status__c = :pValue.getValue() and AccountId__c =: aid ];
if (Count > 0)
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
else
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
}


}

All Answers

priyanka.mv26priyanka.mv26

You have used select query inside for loop..

 

Remove the query from for loop and put it outside the loop.. 

 

Use map, set, list to achieve your solution...

SfDc@123.ax1389SfDc@123.ax1389

Hi Priyanka,

 

         Could you please elaborate in detail as I am new to salesforce Maps are to far away from my concepts , where i have get knowledge. And It would be very greatful If you elaborate and explain in detail.So here I would like you to send a sample set  code for the same.

 

 

Thanks In advance

Newbie. 

priyanka.mv26priyanka.mv26

Do you need the code very urgent?

 

Is that ok for you if I send the modified code tomorrow ??

SfDc@123.ax1389SfDc@123.ax1389

Hi priyanka,

 

                              Thanks for you quick response and can u modify the code by tomaro if possible?My dead line is on monday(6-8-12).And With this query i can get only 6-7 postal codes at a time, where as my requirement is about15 post codes.

 

 

Sincere Thanks in advance

Newbie.

uday annapareddyuday annapareddy

You are calling SOQL in a for loop which pop an error of "too many SOQL queries". You can only use 100 queries in VF page.

 

Modify your for loop like below to reduce the queries and get what u want. 

It works perfectly in my org.

 

for(Schema.PicklistEntry pValue : P)
{
integer[] counts = new integer[50];

if(corporateid != null)
{

for(AggregateResult results : [SELECT postcode__c, Count(status__c)cnt from Custom_report__c where status__c = :pValue.getValue() and Account_LandLord_Id__c =: corporateid group by postcode__c order by postcode__c])
{
system.debug('aggregateresults'+results);

for(integer tl=0;tl<size;tl++)
{
if(st[tl]==results.get('postcode__c'))
{
if(counts[tl]== null)
counts[tl] = 0;
counts[tl] =integer.valueof(results.get('cnt'));
break;
}
else
{
if(counts[tl]== null)
counts[tl] = 0;
counts[tl] +=0;
}

}
system.debug('-----counts-------'+counts);

postcodes.add(string.valueof(results.get('postcode__c')));

status.add(string.valueof(pValue.getValue()));

}
for(integer t2=0;t2<size;t2++)
{
if(counts[t2]!=null)
newcounts.add(string.valueof(counts[t2]));
else
newcounts.add('0');
}
system.debug('-----newcounts-------'+newcounts);

system.debug('-----postcodes after loop-------'+postcodes);
system.debug('-----status after loop-------'+status);
system.debug('-----counts-------'+newcounts);
/* for(integer tl=0;tl<size;tl++)
{
if(st[tl]==postcodes[tl])
count=1;
else
count=0;
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
} */
// items.add(new ChartDataItem(pValue.getValue() , results.format()));
/* List<Account> accts = [select Id, Name, BillingCity, BillingState, BillingPostalCode from Account];
for(Account a : accts) {
// AccountToString aString = new AccountToString( Name = a.Name, BillingAddress = a.BillingAddress, BillingCity = a.BillingCity, BillingState = a.BillingState, BillingPostalCode = a.BillingPostalCode);
leadlist.add(a); // aStringList is a public class variable
}
for(integer i=0; i<5;i++)
{
post = st[i];
system.debug('loop'+i+post);
Count = [select count() from Custom_report__c where status__c = :pValue.getValue() and Account_LandLord_Id__c =: corporateid and Postcode__c =: post];
system.debug('&&&&&&&&&&&count'+count);
if (Count > 0)
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
else
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
}
*/

}

else
{
Count = [select count() from Custom_report__c where status__c = :pValue.getValue() and AccountId__c =: aid ];
if (Count > 0)
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
else
items.add(new ChartDataItem(pValue.getValue() , Count.format()));
}


}

This was selected as the best answer