Applying best practices to SOQL triggers

Question

How can I get this trigger to obey the best practice of not having a SOQL inside a trigger for loop?

trigger MaxCases on Case (before insert) {

    for (Case myCase : Trigger.new) {
        if (myCase.ContactId != null) {
            // Find all cases with this contact created today
            List<Case> casesTodayFromContact = [SELECT Id
                                           FROM Case
                                          WHERE ContactId   = :myCase.ContactId
                                            AND CreatedDate = TODAY];

            // If two are found, close the case     
            if (casesTodayFromContact.size() >= 2) {
                myCase.Status = 'Closed';
            }
        }

Code credits to David Liu's course on SOQL

Answer 1

1. Collect ContactIds from context cases.
2. Group them by ContactId
3. Close context case if contidion is met.

---

//1. Collect ContactIds from context cases.
Set<Id> contactIdsSet = new Set<Id>();
for(Case contextCase :Trigger.new){
    contactIdsSet.add(contextCase.ContactId);
}
contactIdsSet.remove(null);

//2. Group them by `ContactId`
List<AggregateResult> groupedResults = [
    select count(Id)cnt, ContactId
    from Case
    where CreatedDate = today
        and ContactId in :contactIdsSet
    group by ContactId
    ];
Set<Id> casesWithContactIdsToClose = new Set<Id>();
for (AggregateResult ar : groupedResults)  {
    if(Integer.valueOf(ar.get('cnt')) >= 2){
        casesWithContactIdsToClose.add(Id.valueOf((String) ar.get('ContactId')));
    }
}

//3. Close context case if contidion is met.
for(Case contextCase :Trigger.new){
    if(casesWithContactIdsToClose.contains(contextCase.ContactId)){
        contextCase.Status = 'Closed';
    }
}