[SalesForce] Select Count() with dynamic SOQL – how to stay in Governor Limits

I am trying to fix my code so I don't hit any governor limits on my SOQL queries.

Long story short: I am just learning on how to use maps, but I'm trying to figure out how I can do a SelectCount FROM the records IN the map. I have 14 Select count statements that I must summarize in a VF Table. In terms of efficiency, with 3 records I have 13 queries already. I may have 2000 records. This isn't all of my code, but once I get this, I got the rest.

They are generated dynamically. How do I switch my below Code to something Better Written™?

Results = database.Query(Soql)

  //then add it to the map... but now what?
  map<id, object__c> mapResult = new Map<Id, object__c>(Results);



For(wrapperAccount eachWrap:Wrapper){

   String StringID = eachWrap.userObj.Id; //String ID referenced in soql

   if (x > Y){

      eachwrap.NumOfSomething=database.countQuery(soqlNumSomething);

   }elseif(Y>X){

      eachwrap.NumofSomethingElse=database.countQuery(soqlNumSomethingElse);

   }elseif for another 12 times. 

Example of count query: String SOQL = 'Select Count() FROM object__c WHERE 
ID IN :someList AND otherID__c =:stringID';

Each query is unique, cannot be done via rollup summaries, and can't be done on a trigger. It has to be reloaded anytime a change is made on the page, since the user can make changes and I have to reflect that.

Help Please! 😀

Best Answer

You could simply add a group by say ID, then put the totals into a map and pass the value into the wrapper. One query for all

for example:

Map<ID,Decimal> userToCount = New Map<ID,Decimal>();

AggregateResult[] results = [Select Count(something) cnt, 
       OwnerID From Opportunity Group By OwnerID]; //add in the appropriate WHERE clause

for(AggregateResult ar : results)
    userToCount.put((ID)ar.get('id'),(Integer)ar.get('cnt'));

then when constructing wrapper pass in the value from the userToCount.get(THEID) making sure you account for the possibility of null values (no records found int he aggregate query

Related Topic