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
kumara100kumara100 

How to apply governor limit to count() function

I want to get number of records in OpportunityLineItem table for each opportunity Id.
First I use count() function for each OpportunityId within FOR () loop.{passing OpportunityId as parameter}
Integer countRec  = [SELECT count() FROM OpportunityLineItem WHERE OpportunityId = '00690000002LdSrAAK'];

But it fails with "Too many SOQL: 101" when process more records in FOR Loop setup.
So I try to rearrange code as
method
1) Integer  countInteger = [SELECT count() FROM OpportunityLineItem WHERE OpportunityId IN : oppIdsList];
        It gives total number of records for all OpportunityIds. Not number of records for each OpportunityIds.
       
       

2) String oppIdsdbCount = '';
    for (OpportunityWrapper oppObjCount : selectedOpportunityList) {
       oppIdsdbCount = oppIdsdbCount + '\'' + oppObjCount.getOpportunity().Id + '\',';
    }
  oppIdsdbCount = '(' + oppIdsdbCount.substring(0, oppIdsdbCount.length() - 1) + ')';
  String queryRecCount = 'SELECT count() FROM OpportunityLineItem WHERE OpportunityId IN ' + oppIdsdbCount ;
  Integer recCount = Database.countQuery(queryRecCount);
  System.debug('======== recCount =====' + recCount);


  ex : String queryRecCount = 'SELECT count() FROM OpportunityLineItem WHERE OpportunityId IN ('00690000002LdSrAAK', '00690000002LdSsAAK', '00690000002LdStAAK')

   In 2nd method also gives total number of records for all OpportunityIds. Not number of records for each OpportunityIds.
 
  So Is there a way to get List<Integer> for count() function. ? Or any other solution ?
 
  Tx
  Chaminda Kumara

Message Edited by kumara100 on 10-26-2009 04:35 AM
ThomasTTThomasTT

Well, since count() consumes the same resource as select id from ..., I recommend to use parent-child SOQL. You can add any fields of both of Opportunity and OpportunityLineItems.

However, remember, you have another govenor limit "# of query row". You can read only 10,000 record (including the record which is read by count()) in 1 execution. So if you have more than 10,000 records of Opp + OppLineItem, you need to come up with better idea (or filtering condition for Opportunity).

 

ThomasTT

 

List<Opportunity> oppList = [ SELECT id, name, ..., ( SELECT id FROM OpportunityLineItems ) FROM Opportunity WHERE (condition to filter Opportunity itself) ]; for(Opporunity opp : oppList) { System.debug('Opportunity ' + opp.name + ' has ' +opp.OpportunityLineItems.size() + ' Line Items(s)'); }

 

 

 

prageethprageeth

Hi Kumara;

You can use following method to fix your problem.

 

 

public class TemplateController{

public Integer getCount(){

Integer countInteger = 0;

String idString = '';

List<Opportunity> opportunityList = getOpps();

for (Opportunity o : opportunityList) {

idString += 'OpportunityId = \'' + o.Id + '\'' + ' OR ';

}

idString = idString.substring(0, idString.length() - 4);

String query = 'SELECT count() FROM OpportunityLineItem where ' + idString;

countInteger = database.countQuery(query);

return countInteger;

}

 

public Opportunity[] getOpps() {

Opportunity[] oArr = [Select id from Opportunity];

return oArr;

}

}

 

 

 

Message Edited by prageeth on 10-26-2009 09:34 PM