How to avoid SOQL Query in the for loop in this scenario

Question

How to avoid the SQL Query in this case

 public static void sendSMS(List<Case> newList){
        List<Case> selectedCases =[SELECT Id ,
                                           Department__c,
                                           CaseNumber,
                                           Customer_mobile__c,
                                           Case_Category__c,
                                           Sub_Category__c  ,
                                           Unit__c , 
                                           Unit__r.Project__c , 
                                           Account.PersonMobilePhone ,
                                           Project__c,
                                           Project_Name__c,
                                           Description,
                                           CreatedBy.Profile.Name
                                    FROM Case
                                    WHERE Id IN :newList]; 

                                    List<User> communityManagmentUsers= [SELECT Id, Name, MobilePhone,Email
                                    FROM User 
                                    WHERE id IN (SELECT UserOrGroupid FROM Groupmember WHERE Group.Name = 'Community Management - SMS')];
        
        for(Case caseItem : selectedCases){
            Map<String, String> smsMessageMap = new Map<String, String>();
                if(caseItem.Department__c == 'Community Management'){
                    //Internal SMS
                    if (caseItem.Case_Category__c == 'Incident'){
                            project__c project=[SELECT Id,Name ,CM_Project_Manager__c,FM_Project_Manager__c FROM Project__c WHERE Name=:caseItem.Project_Name__c OR Id =:caseItem.Project__c LIMIT 1];
                            
                            List<User> managerUsers = [SELECT Id , MobilePhone , Email FROM User WHERE id=:project.CM_Project_Manager__c OR id=:project.FM_Project_Manager__c];
                            communityManagmentUsers.addAll(managerUsers);

                             for(User user : communityManagmentUsers){ 
                                String smsBody =  'Incident Case has been created' + ' '+ caseItem.CaseNumber + ' ' + 'Sub Category: ' + caseItem.Sub_Category__c + ' '+'Project: ' + project.Name +'. \n' +
                                                     URL.getSalesforceBaseUrl().toExternalForm()+'/'+caseItem.Id + ' .' ;
                                smsMessageMap.put(user.MobilePhone,smsBody);
                                sendEmail(user.email,caseItem);
                                }

                            isMsgSent(smsMessageMap);
                    }
                    //External SMS
                     else{
                        if(caseItem.CreatedBy.Profile.Name.contains('Call Centre')){
                              String messageBody = 'Thank you for calling. your case number ' + caseItem.CaseNumber + '.';
                             if(isValidMobileNumber(caseItem.Customer_mobile__c)){
                                  smsMessageMap.put(caseItem.Customer_mobile__c , messageBody);
                                  isMsgSent(smsMessageMap);
                              }
                              else{
                                  if(isValidMobileNumber(caseItem.Account.PersonMobilePhone)){
                                      smsMessageMap.put(caseItem.Account.PersonMobilePhone, messageBody);
                                      isMsgSent(smsMessageMap);
                                  }
                              
                              }
                         }
                     }
                }
           
        }

Answer 1

Your code needs to be bulkified. It's more than just considering the SOQL inside the loop, it's the entire design of your method.

First, the related project data can be pulled into the main query, and the management users from there. Then, we delay sending the emails until the end, as that's also a limited resource. We also wait to send the SMS messages until the end as well. I made a few guesses in your code, and this code is certainly not bullet proof, but here's what the code might look like:

public static void sendSMS(List<Case> newList){
    List<Case> selectedCases =[
        SELECT 
            Department__c,
            CaseNumber,
            Customer_mobile__c,
            Case_Category__c,
            Sub_Category__c  ,
            Unit__c , 
            Unit__r.Project__c , 
            Account.PersonMobilePhone ,
            Project__c,
            Project_Name__c,
            Description,
            CreatedBy.Profile.Name,
            (SELECT 
                CM_Project_Manager__c.MobilePhone, 
                CM_Project_Manager__r.Email,
                FM_Project_Manager__r.MobilePhone,
                FM_Project_Manager__r.Email
            FROM Projects__r)
        FROM Case
        WHERE Id IN :newList
        AND Department__c = 'Community Management'
        AND (Case_Category__c = 'Incident' OR CreatedBy.Profile.Name LIKE '%Call Centre%')
    ];

    List<User> communityManagmentUsers= [
        SELECT Id, Name, MobilePhone,Email
        FROM User 
        WHERE id IN (SELECT UserOrGroupid FROM Groupmember WHERE Group.Name = 'Community Management - SMS')
    ];
    
    Map<String, String> smsMessageMap = new Map<String, String>();
    Messaging.EmailMessage[] emails = new Messages.EmailMessage[0];
    for(Case caseItem : selectedCases){
        if (caseItem.Case_Category__c == 'Incident'){
            Project__c project= caseItem.Project__r[0];
            List<User> messageUsers = communityManagmentUsers.clone();
            if(caseItem.FM_Project_Manager__r.Id != null) {
                messageUsers.add(caseItem.FM_Project_Manager__r);
            }
            if(CaseItem.CM_Project_Manager__r.Id != null) {
                messageUsers.add(CaseItem.CM_Project_Manager__r);
            }
            for(User user : messageUsers) {
                String smsBody =  'Incident Case has been created '+ caseItem.CaseNumber + ' Sub Category: ' + caseItem.Sub_Category__c + ' Project: ' + project.Name +'.\n' +
                                        URL.getSalesforceBaseUrl().toExternalForm()+'/'+caseItem.Id + ' .' ;
                smsMessageMap.put(user.MobilePhone,smsBody);
                emails.add(createEmailToSend(user.email,caseItem));
            }
        }else{
        //External SMS
            if(caseItem.CreatedBy.Profile.Name.contains('Call Centre')){
                String messageBody = 'Thank you for calling. your case number ' + caseItem.CaseNumber + '.';
                if(isValidMobileNumber(caseItem.Customer_mobile__c)){
                    smsMessageMap.put(caseItem.Customer_mobile__c , messageBody);
                } else if(isValidMobileNumber(caseItem.Account.PersonMobilePhone)){
                    smsMessageMap.put(caseItem.Account.PersonMobilePhone, messageBody);
                }
            }
        }
    }
    Messaging.sendEmail(emails, false);
    isMsgSent(smsMessageMap);
}

Notice we replace sendEmail with createEmailToSend. There, we only create the Messaging.SingleEmailMessage object, and delay sending until the end. We also clone the community management emails list each time to avoid sending messages to the wrong people, as could happen in your original code, since the list is never reset.

Finally, we filter out records that don't meet any criteria, eliminating the number of rows we need to process and an extra if statement.