SOQL limit exceeds for tracking cases

Question

I've developed apex classes for case analysis. I've posted the main part of the apex class. This is to track the action in the case. I'm querying a list of cases from a class and pass those Ids to this class to track everything about the case. But SOQL was hitting the limit (100) in just 6 cases. So I created a batch class and invoked this class to increase the Limits (200). But it reaches the limit in 10th case. But we need to run about 40+ cases every day. I think I've not coded in a good way having so many SOQL queries. Any help would be appreciated. Thanks

    public class CaseFollow {

    public Case ccase;
    public List<Follow> allFeed {get; set;}
    public static BusinessHours stdbh = [SELECT id 
                                            FROM businesshours 
                                            WHERE isDefault = true];
    public double dbTotalAge {get; set;}
    public double dbFinalAge;
    public datetime dtCaseEndDate;
    public Map<ID,Profile> profileIdToProfileMap = new Map<ID,Profile>([select id, userLicense.name from Profile]);

    public CaseFollow(Id id) {
        allFeed = new List<Follow>();
        ccase = [SELECT CreatedDate, ClosedDate, Status
              FROM Case 
              WHERE Id =: id];

        dtCaseEndDate = ccase.ClosedDate;

        if(dtCaseEndDate == null) {
            dtCaseEndDate = Date.TODAY();
            dbTotalAge = UtilityCaseAge.Calculate(ccase.CreatedDate, dtCaseEndDate);
        } else {
            dbTotalAge = UtilityCaseAge.Calculate(ccase.CreatedDate, ccase.ClosedDate);
        }

        Map<Integer, datetime> chOldMap = new Map<Integer, datetime>();
        Map<Integer, datetime> chNewMap = new Map<Integer, datetime>();
        double StatusCloseToOpen;
        double statusGapToRemove = 0;
        Integer intOldMapCount = 1;
        Integer intNewMapCount = 1;
        List<CaseHistory> caseHistoryList = [SELECT Field, OldValue, NewValue, createdDate, createdby.name, createdby.Profile.Id 
                                    FROM CaseHistory 
                                    WHERE Case.Id =: ccase.Id
                                    AND Field = 'Status'
                                    ORDER BY createdDate ASC];

        for(CaseHistory ch : caseHistoryList) {
            String strStatusOldValue = String.valueOf(ch.OldValue);
            String strStatusNewValue = String.valueOf(ch.NewValue);
            DateTime dtActionTime = ch.CreatedDate;
            String statusChangedBy = ch.createdby.name;
            boolean blnStatusChangeAt;
            //To exclude the limit between closed and opened action
            if(ch.NewValue == 'Closed') {
                chOldMap.put(intOldMapCount, ch.CreatedDate);
                ++intOldMapCount;
                allFeed.add(new Follow(dtActionTime, statusChangedBy, profileIdToProfileMap.get(ch.createdby.Profile.Id).userLicense.name, false));
            }
            if(ch.OldValue == 'Closed') {
                chNewMap.put(intNewMapCount, ch.CreatedDate);
                ++intNewMapCount;
                allFeed.add(new Follow(dtActionTime, statusChangedBy, profileIdToProfileMap.get(ch.createdby.Profile.Id).userLicense.name, true));
            }
        }

        System.debug('  OLD MAP ------- ' + chOldMap);
        System.debug('  NEW MAP ------- ' + chNewMap);

        for(Integer i = 1;;++i) {
            if(chNewMap.get(i) == null) {
                break;
            }
            StatusCloseToOpen = BusinessHours.diff(stdbh.id, chOldMap.get(i), chNewMap.get(i)) / 1000;  //SECONDS
            System.debug('  OldMap Value -------- ' + chOldMap.get(i) + '***NewMap Value -------- ' + chNewMap.get(i) + ' Difference ------- ' + StatusCloseToOpen + ' seconds');
            statusGapToRemove = statusGapToRemove + StatusCloseToOpen;
            System.debug('---- statusGapToRemove ----' + statusGapToRemove);
        }

        dbFinalAge = dbTotalAge - statusGapToRemove;
        System.debug('  Final Total Case Age : ' + dbFinalAge);

        for(CaseComment casecom: [SELECT createdDate, CreatedBy.Name, CreatedBy.profile.Id 
                             FROM CaseComment 
                             WHERE ParentId =: ccase.Id]) {
            allFeed.add(new Follow(casecom.createdDate, casecom.CreatedBy.Name, profileIdToProfileMap.get(casecom.createdby.Profile.Id).userLicense.name, false));
        }
        for(EmailMessage emailmsg: [SELECT CreatedDate, CreatedBy.Name, CreatedBy.profile.Id 
                              FROM EmailMessage 
                              WHERE ParentId =: ccase.Id]) {
            allFeed.add(new Follow(emailmsg.createdDate, emailmsg.CreatedBy.Name, profileIdToProfileMap.get(emailmsg.createdby.Profile.Id).userLicense.name, false));
        }
        for(Attachment attachs: [SELECT lastmodifieddate, CreatedBy.Name, CreatedBy.profile.Id 
                             FROM Attachment 
                             WHERE ParentId =: ccase.Id]) {
            allFeed.add(new Follow(attachs.lastmodifieddate, attachs.CreatedBy.Name, profileIdToProfileMap.get(attachs.createdby.Profile.Id).userLicense.name, false));
        }
        allFeed.sort();
        System.debug('    CONSTRUCTOR SORTED INTERACTION    ');
        TrackTotal();
    }

public class Follow implements Comparable {
    public dateTime activityDate {get; set;}
    public string Createdby {get; set;}
    public string theUserLicense {get; set;}    
    public boolean blnStatusChangeAt;

    Follow(dateTime activityDate, string Createdby, string theUserLicense, boolean blnStatusChangeAt) {
        this.activityDate = activityDate;
        this.Createdby = Createdby;
        this.theUserLicense = theUserLicense;
        this.blnStatusChangeAt = blnStatusChangeAt;
    }
    public Integer compareTo(object o) {
        Follow tl = (Follow) o;

        if(tl.activityDate < this.activityDate) return 1;
        else if (tl.activityDate  > this.activityDate) return -1;
        else return 0;            
    }
}

//Id from another class is passed
public static void passCaseIdToConstructor(Id id) {
    CaseFollow m = new CaseFollow(id);
    System.debug('    passCaseIdToConstructor passed to constructor!   ');
}

//rest of the code
}

Batch Class

global class CaseSelectorBatch implements Database.Batchable<SObject> {

    global String caQuery;

    global Database.QueryLocator start(Database.BatchableContext bcMain) {

        caQuery = 'SELECT Id, Status, CreatedDate, ClosedDate FROM Case WHERE createdDate = today';
        return Database.getQueryLocator(caQuery);
    }

    global void execute(Database.BatchableContext bcMain, List<SObject> batchRecords) {

        CaseSelector cs = new CaseSelector();
        cs.passCaseIdToConstructor();
    }

global void finish(Database.BatchableContext bcMain) {
    AsyncApexJob aaJob = [SELECT TotalJobItems,
                          NumberOfErrors,
                          ExtendedStatus
                          FROM AsyncApexJob WHERE Id =: bcMain.getJobId()];

    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();     
    String[] toAddress = new String[]{'test@test.com'}; //test@test.com is not an existing email
    mail.setToAddresses(toAddress);                     
    mail.setSenderDisplayName('BatchApex Notification');

    if(aaJob.NumberOfErrors > 0) {
        mail.setSubject('Batch Apex Error: CaseSelectorBatch');
        mail.setPlainTextBody('Processed' + aaJob.TotalJobItems + 'Case batches with ' + aaJob.NumberOfErrors + ' failures. Description : '+ aaJob.ExtendedStatus + '. Completed at' + String.valueOf(datetime.now()));
    } else {
        mail.setSubject('Batch Apex Success: CaseSelectorBatch');
        mail.setPlainTextBody('Processed ' + aaJob.TotalJobItems + ' Case batches with 0 failures. Completed at ' + String.valueOf(datetime.now()));
    }
    Messaging.sendEmail( new Messaging.SingleEmailMessage[]{mail});
}

}

Answer 1

OK, the reason you are exceeding the SOQL limit is because you are querying for Profile over and over again within the for (CaseComment ...) loop, the for (EmailMessage ...) loop and the for (CaseHistory ... ) loop.

The accepted best practice would be to query for Profile only once and stash the results in a map

Map<ID,Profile> prfIdToProfileMap = new Map<ID,Profile> 
          {[select id, userLicense.name from Profile]};

then, when you need the particular profile within your for loop, you just reference the map

For example, this line within the CaseHistory loop:

allFeed.add(new Follow(dtActionTime, statusChangedBy, theUserLicense, false));

becomes

allFeed.add(new Follow(dtActionTime, statusChangedBy, 
           prfIdToProfileMap.get(ch.createdby.Profile.Id).userLicense.name, false));

Thus, you fetch all Profiles in one go -- there won't be that many so I didn't bother to use a filter (you could do that if you really have hundreds of profiles) Thus, your class boils down to 6 SOQL calls - and always 6 (if I counted correctly).

Update 1 - In my rush to help you, I omitted the issue of handling the batch aspects of your problem statement. As @jsfdev01 says, the queries need to be all done outside of the class in the batch execute() method. You can actually do all of these queries in one query as shown here

List<Case> csList = [select id, ..,
                (select id, ... from Histories),
                (select id, ... from CaseComments),
                (select id, ... from CombinedAttachments),
                (select id, ... from EmailMessages),
                from Case where id IN :scopeArgumentasPassedIntoBatchExecute];

and then loop through csList, passing each Case as well as the map of all Profiles into your class (which then has access to all of the related lists plus the Profile map)

You'll need to rejigger CaseFollow to not be doing all the work in the constructor. Hopefully this gets you onto the right track

Update 2 - Here's a bit more explanation of how to approach the coordination of the batch execute() method and your worker class

global void execute(Database.BatchableContext bcMain, List<SObject> batchRecords) {
   system.debug('*****************Execute CaseSelector********');
   Map<ID,Profile> prfIdToProfileMap = new Map<ID,Profile> 
          {[select id, userLicense.name from Profile]}; // Could be a stateful var and moved into the batchable's constructor
   List<Case> csList = [select id, ..,
            (select id, ... from Histories),
            (select id, ... from CaseComments),
            (select id, ... from CombinedAttachments),
            (select id, ... from EmailMessages),
            from Case where id IN :batchRecords];
   CaseFollow cf = new CaseFollow();
   List<Follow> fList = new List<Follow> ();
   for (Case cs: csList)
       fList.addAll(cf.doTheWork(cs,prfIdToProfileMap));
   insert fList;  // add all the Follows  

}

and CaseFollow has a method doTheWork() that returns a list of Follow. This is a reworked version of your original CaseFollow constructor. I'm assuming you want the batch to insert the constructed Follows but I may have this wrong.

I'll leave it to you to rework your CaseFollow class to a) not do any SOQL or DML and b) work on a single Case plus its related records and return, in the doTheWork() method, all the Follow objects that need to be inserted. You should have what you need by now to avoid running out of SOQL queries in a batch. Good luck.

Update 3 - Actually, the SOQL inside of the execute() method should be moved to the start() method and let SFDC handle this but I ran out of gas on this one.