0

I have a strange problem. Let me explain the thing which I am trying to achieve.

Am trying to fetch more than 50000 records and perform an aggregate query from apex controller of lightning component am pulling the record count and if there are more than 50000 am calling the batch apex.

From the batch apex, as we are using aggregate functions in Dynamic SOQL query something like this.

String query= 'SELECT SUM(field)sum,SUM(field)sum1,SUM(field)sum2,SUM(field)sum3,SUM(field)sum4, SUM(field)sum5 FROM <object name> WHERE <field>=<id> ';

and then actual thing begins where there are certain multiple select fields if they are not empty then accordingly the dynamic SOQL query have to vary like this :

if(<multiselect_field__C!= null && multiselect_field__C.size()>0) {
    system.debug(multiselect_field__C);
    query= query+ ' AND multiselect_field__C IN :value LIMIT 5000';
}

batch class

global class audienceBatch implements  Database.Batchable<AggregateResult>,Database.Stateful,Iterator<AggregateResult>
{   
    global Id ASCid;
   global List<String> Locations;
    //System.debug(Locations)
    global list<string> AgeRanges;
    global list<string> segments;
   global        List<Integer> spentList = new List<Integer>();
  //  List<String> Locations 
Integer index;
    List<AggregateResult> aggregates;
    global Iterable<AggregateResult> start(Database.BatchableContext  BC) {
        System.debug('audid'+ASCid);

        String strQuery = 'SELECT SUM(Customer_Count__c)sum,SUM(Competitor_1_Spend__c)sum1,SUM(Competitor_2_Spend__c)sum2,SUM(Competitor_3_Spend__c)sum3,SUM(Competitor_4_Spend__c)sum4, SUM(Competitor_5_Spend__c)sum5 FROM Audience_Aggregate_Data_Set__c WHERE Audience_Selection_Criteria__c =:ASCid  group by id ';
     system.debug(Locations);
        if(Locations!= null && Locations.size()>0) {
            system.debug(Locations);
            strQuery = strQuery+ ' AND Location__c IN :Locations LIMIT 5000';
        }
        //System.debug(AggregateResultIterable(strQuery));
          List<AggregateResult> aggregates = Database.query(strQuery);
        System.debug(aggregates+'aggregates');
         if(Integer.valueOf(aggregates[0].get('sum')) != null) {
                spentList.add(Integer.valueOf(aggregates[0].get('sum')));
            }
            else{spentList.add(0);}
        System.debug(spentList);
        return new AggregateResultIterable(strQuery);

    }
 public Boolean hasNext()
    {
        return index < aggregates.size();
    }
     public AggregateResult next()
    {
        return aggregates[index++];
    }

    global void execute(Database.BatchableContext BC, List<AggregateResult> accList) {

        // process each batch of records
try {

  String strQuery = 'SELECT SUM(Customer_Count__c)sum,SUM(Competitor_1_Spend__c)sum1,SUM(Competitor_2_Spend__c)sum2,SUM(Competitor_3_Spend__c)sum3,SUM(Competitor_4_Spend__c)sum4, SUM(Competitor_5_Spend__c)sum5 FROM Audience_Aggregate_Data_Set__c WHERE Audience_Selection_Criteria__c =   ' + '\'' + ASCid + '\'GROUP BY Id LIMIT 2000';
List<AggregateResult> sobjList = Database.query(strQuery);
    //AggregateResult ar = (AggregateResult)acc;
    System.debug(sobjList);
        for(AggregateResult  acc : sobjList)
        {        
                            //    String strQuery = 'SELECT SUM(Customer_Count__c)sum,SUM(Competitor_1_Spend__c)sum1,SUM(Competitor_2_Spend__c)sum2,SUM(Competitor_3_Spend__c)sum3,SUM(Competitor_4_Spend__c)sum4, SUM(Competitor_5_Spend__c)sum5 FROM Audience_Aggregate_Data_Set__c WHERE Audience_Selection_Criteria__c =: ASCid GROUP BY Id';
                   // String strQuery = 'SELECT SUM(Customer_Count__c)sum,SUM(Competitor_1_Spend__c)sum1,SUM(Competitor_2_Spend__c)sum2,SUM(Competitor_3_Spend__c)sum3,SUM(Competitor_4_Spend__c)sum4, SUM(Competitor_5_Spend__c)sum5 FROM Audience_Aggregate_Data_Set__c WHERE Audience_Selection_Criteria__c =   ' + '\'' + ASCid + '\'GROUP BY Id LIMIT 50000';
AggregateResult ar1 = (AggregateResult)acc;
        }



        } catch(Exception e) {
            System.debug('exceptionin vbatch'+e);
        }

    }   

    global void finish(Database.BatchableContext BC) {
         System.debug(' records processed. Shazam!');
        AsyncApexJob job = [SELECT Id, Status, NumberOfErrors, 
            JobItemsProcessed,
            TotalJobItems, CreatedBy.Email
            FROM AsyncApexJob
            WHERE Id = :bc.getJobId()];
        // call some utility to send email
        System.debug('job'+job);
  }
}

here where the problems as it is aggregate query and it has to be grouped in batch apex and group by and limit have to be used and how can we use having as having clause has to be also aggregated.

This query is giving me nightmares.

4
  • Is this multiselect_field__C is a multi picklist field? Sep 19 '19 at 8:52
  • @Nishadkallingal Yes Sep 19 '19 at 9:06
  • Can you edit the question and post your batch class,.you can't use like this "IN :value " in a multi picklist field filter, you should have to use "includes" Sep 19 '19 at 10:26
  • @Nishadkallingal attached the batch class Sep 19 '19 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.