How to avoid Too Many SOQL Queries

Question

I know that the reason for this it's because I have a query that it's executed inside a loop, but I don't see how to redesign the code to avoid this. Any help will be appreciated.

Basically, I have a scheduled class that queries cases with certain criteria and checks if they have a public article attached, if the article is public, then an email should be sent to the case contact.

This is part of my scheduled class code.

public void sendKBArticle(){

     Boolean hasPublicArticles;
     Id EmailTemplateId='00XP0000000MHPI'; 

     Map<id,case> cMap = new Map<id,case>(
     [Select Id,ContactId from Case   Where Owner.Type='User' AND IsClosed=false AND KB_Article_Sent__c=false 
         AND Status NOT IN ('Pending Bug Fix','Solution Provided','Escalated','Reopened')
             AND RecordType.Name IN ('Internal', 'Customer Portal') AND CreatedDate  = LAST_N_DAYS:3 ]);

        if(!cMap.IsEmpty()){

           for(Case cnt : cMap.values()) {

                ArticlePublicLinksonEmail cpkb=new ArticlePublicLinksonEmail();

                hasPublicArticles=cpkb.getPublicArticles(cnt.id);

                if(hasPublicArticles){

                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setTemplateId(EmailTemplateId);
                    mail.setTargetObjectId(cnt.ContactId);
                    mail.setWhatId(cnt.Id);
                    mail.setSaveAsActivity(true);  
                    Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                    Cnt.KB_Article_Sent__c=true;
                }
            }




           try {
                 update cMap.values();
           } catch (DMLException e){         

                .....some code
           }

        }

     }    


}

As you can see, inside the loop I am passing the object id to another method, this method uses this case id to query if the case has a public article attached. Therefore, if I have many cases I hit the query limit.

Any ideas how to fix this or any suggestions in how to approach this in a different way?

Thanks!

Answer 1

@MartinPeters is on the right track: getPublicArticles needs to work in bulk somehow. You need to call getPublicArticles outside of the loop, since it's doing a query every time. So collect Case Ids before the getPublicArticles and loop through the results to deal with public articles afterward.

Also, you are calling Messaging.sendEmail() inside your loop, which will also hit the governor limit eventually. Instead, keep a running list<Messaging.SingleEmailMessage>, and do Messaging.sendEmail() once the list is full.

set<Id> caseids = cMap.keyset();
//assuming this method is "private map<Id, Boolean> getPublicArticles(set<Id> caseids)"
map<Id, Boolean> caseid2HasPublicArticlesmap = getPublicArticles(caseids);

list<Messaging.SingleEmailMessage> emailstosend = new list<Messaging.SingleEmailMessage>();
for(Case cnt : cMap.values()) {
    if (caseid2HasPublicArticlesmap.get(cnt.Id) == TRUE) {
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                mail.setTemplateId(EmailTemplateId);
                mail.setTargetObjectId(cnt.ContactId);
                mail.setWhatId(cnt.Id);
                mail.setSaveAsActivity(true);  
                //Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                emailstosend.add(mail);
                Cnt.KB_Article_Sent__c=true;
    }
}
Messaging.sendEmail(emailstosend);